Einleitung

Das Ziel dieser Case Study ist die Verdeutlichung und Aufklärung von und über Produktionsproblemen eines Komponentenherstellers “111”, das Ledersitze für Fahrzeughersteller aus eingekauften Einzelteilen zusammenbaut. Nach Hinweis durch den Fahrzeughersteller auf Kundenreklamationen sollen von allen beteiligten Schritten der Kette vom Einzelteilhersteller bis zum Kunden Daten zu defekten Fahrzeugen zusammengetragen, aufbereitet und ausgewertet werden.

Zusätzlich soll ein Tool generiert werden mit dessen Hilfe Fahrzeughalter anhand ihrer Fahrzeugnummer überprüfen können ob ihr Fahrzeug von den Defekten betroffen ist.

Benötigte Libraries

# check if needed packages are installed on the using machine. Install them, if not.
# load packages with library()

if(!require(knitr)){
  install.packages("knitr")
}
library(knitr)

if(!require(kableExtra)){
  install.packages("kableExtra")
}
library(kableExtra)

if(!require(magrittr)){
  install.packages("magrittr")
}
library(magrittr)

if(!require(tidyverse)){
  install.packages("tidyverse")
}
library(tidyverse)

if(!require(readr)){
  install.packages("readr")
}
library(readr)

Importieren der Daten

Auswahl der relevanten Daten

Die Aufgabe bestand grundsätzlich darin, aus Sicht des Herstellers “111” für die Komponente Premium-Ledersitze alle Fahrzeuge mit fehlerhaften Komponenten des entsprechenden Typs bzw. fehlerhaften Einzelteilen, die in jenen Komponenten enthalten sind, zu identifizieren und Fahrzeughalter und -hersteller (OEM) zu isolieren und informieren.

Dazu galt es zunächst, alle für die Analyse und Fehleridentifizierung relevanten Datensätze zu importieren. Diese entsprechen der Komponente “K2LE1” mit den Einzelteilen “T11”, “T14” und “T15” sowie “K2LE2” mit den Einzelteilen “T16”, “T19” und “T20”, den Fahrzeugen “Typ11” und “Typ12” des Herstellers “OEM1” sowie “Typ21” und “Typ22” des Herstellers “OEM2” und die entsprechenden Geodaten für Werke der “Tier2” (Einzelteile), “Tier1” (Komponenten) und “OEM”. Für die Zuordnung zu Fahrzeughaltern und die spätere visuelle Darstellung der räumlichen Verteilung der Defekte waren die Datensätze “Zulassungen” mit den entsprechenden Geodaten “Gemeinden” relevant.

Um die Datensätze zu einem zusammenfassen, standen die relationalen Tabellen “Bestandteile_Komponente” (für Einzelteile und Komponenten) und “Bestandteile_Fahrzeug” (für Komponenten und Fahrzeuge) zur Verfügung.

Importierte Daten
Einzelteile Komponenten und Fahrzeuge Relationale Tabellen Zulassungen und Geodaten
Einzelteil_T11.txt Komponente_K2LE1.txt Bestandteile_Komponente_K2LE1.csv Zulassungen_alle_Fahrzeuge.csv
Einzelteil_T14.csv Komponente_K2LE2.txt Bestandteile_Komponente_K2LE2.csv Tier1_Werke_2017-07-11_v1.2_TrR.csv
Einzelteil_T15.csv Fahrzeuge_OEM1_Typ11.csv Bestandteile_Komponente_OEM1_Typ11.csv Tier2_Werke_2017-07-11_v1.2_TrR.csv
Einzelteil_T16.txt Fahrzeuge_OEM1_Typ12.csv Bestandteile_Komponente_OEM1_Typ12.csv OEM_Werke_2017-07-04_TrR.csv
Einzelteil_T19.csv Fahrzeuge_OEM1_Typ21.csv Bestandteile_Komponente_OEM2_Typ21.csv Geodaten_Gemeinden_v1.2_2017-08-22_TrR.csv
Einzelteil_T20.txt Fahrzeuge_OEM1_Typ22.csv Bestandteile_Komponente_OEM2_Typ22.csv

Vorgehensweise zur Importierung der Datensätze

Die zur Verfügung gestellten Dateien wurden mit dem readr-Package importiert. Die Daten lagen in unterschiedlichen Datei-Formaten (.txt und .csv) und unterschiedlichen Strukturen (verschiedene Delimiter und Spaltenanzahlen) vor.

Die Schwierigkeit beim Import der Einzelteile “T11”, “T16” und “T20” sowie der Komponenten “K2LE1” und “K2LE2” lag zunächst im .txt-Format und in unregelmäßigen Delimitern zwischen den Spalten und Zeilen. Deshalb mussten die genannten Daten zunächst mit der Funktion read_file() als string eingelesen und in einem Objekt zwischengespeichert werden, um Performance-Probleme und Parsing-Fehler zu vermeiden. Danach wurden die Delimiter zwischen den Spalten mit für .csv-Dateien üblichen Delimitern ersetzt und dann der Delimiter zwischen den Zeilen mit einem Line-Break ertsetzt. Außerdem hat in den betroffenen Datensätzen jeweils die erste Variable gefehlt, während die Spalte jedoch mit Werten befüllt war, was beim Einlesen zu Parsing-Fehlern führen würde. Deshalb wurde mit paste() eine entsprechende Variable mit Namen “X1_1” oder “X2” an den Beginn des Datensatzes als Platzhalter eingefügt. Hinterher konnten die fertig vorbereiteten Datensätze mit read_delim() bzw. read_csv2() importiert werden. Die Platzhalterobjekte wurden im Anschluss gelöscht, um Speicherressourcen freizugeben.

# read relevant Einzelteil files with exotic delimiters in txt-files (T11, T16, T20) as string (for better performance)
einzelteil_t11_string <- read_file("Data/Einzelteil/Einzelteil_T11.txt")
einzelteil_t16_string <- read_file("Data/Einzelteil/Einzelteil_T16.txt")
einzelteil_t20_string <- read_file("Data/Einzelteil/Einzelteil_T20.txt")

# replace exotic delimiters between columns
einzelteil_t11_string <- gsub("\t", ",", einzelteil_t11_string)
einzelteil_t16_string <- gsub(" \\| \\| ", ",", einzelteil_t16_string)
einzelteil_t20_string <- gsub(" \\| \\| ", ",", einzelteil_t20_string)

# paste missing column name at first position to avoid parsing error when read_csv will be used
einzelteil_t11_string <- paste("X1_1", einzelteil_t11_string, sep = ",")
einzelteil_t16_string <- paste("X1_1", einzelteil_t16_string, sep = ",")
einzelteil_t20_string <- paste("X1_1", einzelteil_t20_string, sep = ",")

# replace exotic delimiters between rows with line breaks
einzelteil_t11_string <- gsub("", "\n", einzelteil_t11_string)
einzelteil_t16_string <- gsub("\t", "\n", einzelteil_t16_string)
einzelteil_t20_string <- gsub(" ", "\n", einzelteil_t20_string)

# Import final csv-files
# read_csv (delim = ","), coerce col_type for t16 because data is split on several columns which causes parsing errors
t11 <- read_delim(einzelteil_t11_string, delim = ",")
t16 <- read_delim(einzelteil_t16_string, delim = ",", col_types = c("nncDnnnDncDnnnDncDnnnDn"))
t20 <- read_delim(einzelteil_t20_string, delim = ",")

# remove unused objects and return memory
einzelteil_t11_string <- ""
einzelteil_t16_string <- ""
einzelteil_t20_string <- ""
gc()

# read file for Komponenete_K2LE1 and replace delim with single character delim
tx  <- read_file("Data/Komponente/Komponente_K2LE1.txt")
tx2 <- gsub(pattern = "II", replace = ";", x = tx, fixed = TRUE)
# add column name 
tx3 <- paste("X2", tx2, sep = ";")
# replace exotic separator with newline
out <- gsub(pattern = "", replace = "\n", x = tx3, fixed = TRUE)
# write output in temp file 
myFile <- tempfile()
writeLines(out, con=myFile)

# read final komponente_k2le1
komponente_k2le1 <- read_csv2(myFile, col_types = c("nncDnnnDncDnnnDn"))

# read file for Komponenete_K2LE2 because first column name is missing
temp1 <- read_file("Data/Komponente/Komponente_K2LE2.txt")
# add first column name
temp2 <- paste("X2", temp1, sep = "\\")
# write into temporary file
myFile2 <- tempfile()
writeLines(temp2, con=myFile2)

# read final komponente_k2le1
komponente_k2le2 <- read_delim(myFile2, delim = "\\") 

# remove unused objects and free memory
temp1 <- ""
temp2 <- ""
myFile2 <- ""
gc()

Alle anderen Datensätze konnten ohne weitere Eingriffe entsprechend ihres Delimiters mit read_csv() (Delimiter “;”) oder read_csv2() (Delimiter “,”) importiert werden. Bei den Datensätzen “T15”, T16“,”K2LE1" und den Geodaten mussten die Klassen für die einzelnen Variablen bzw. Spalten per col_types() vorgegeben werden, da es sonst aufgrund einer hohen Zahl fehlender Werte in diesen Datensätzen zu Parsing-Fehlern kam.

# read_csv2 (delim = ";") and read_csv (delim = ",") for remaining relevant Einzelteil datasets, coerce col_type for t15 because data is split on several columns which causes parsing errors
t19 <- read_csv("Data/Einzelteil/Einzelteil_T19.csv")
t14 <- read_csv2("Data/Einzelteil/Einzelteil_T14.csv")
t15 <- read_csv2("Data/Einzelteil/Einzelteil_T15.csv", col_types = c("nncDnnnDncDnnnDn"))

# read Bestandteile_Komponenten for relevant Kompenenten (K2LE1, K2LE2)
bestandteile_k2le1 <- read_csv2("Data/Komponente/Bestandteile_Komponente_K2LE1.csv")
bestandteile_k2le2 <- read_csv2("Data/Komponente/Bestandteile_Komponente_K2LE2.csv")

# read Fahrzeuge, use right function for different delims (, and ;) 
fahrzeug_oem1_typ11 <- read_csv("Data/Fahrzeug/Fahrzeuge_OEM1_Typ11.csv")
fahrzeug_oem1_typ12 <- read_csv2("Data/Fahrzeug/Fahrzeuge_OEM1_Typ12.csv")
fahrzeug_oem2_typ21 <- read_csv("Data/Fahrzeug/Fahrzeuge_OEM2_Typ21.csv")
fahrzeug_oem2_typ22 <- read_csv2("Data/Fahrzeug/Fahrzeuge_OEM2_Typ22.csv")

# read Bestandteile_Fahrzeuge
bestandteile_oem1_typ11 <- read_csv2("Data/Fahrzeug/Bestandteile_Fahrzeuge_OEM1_Typ11.csv")
bestandteile_oem1_typ12 <- read_csv2("Data/Fahrzeug/Bestandteile_Fahrzeuge_OEM1_Typ12.csv")
bestandteile_oem2_typ21 <- read_csv2("Data/Fahrzeug/Bestandteile_Fahrzeuge_OEM2_Typ21.csv")
bestandteile_oem2_typ22 <- read_csv2("Data/Fahrzeug/Bestandteile_Fahrzeuge_OEM2_Typ22.csv")

# read zulassungen
zulassungen <- read_csv2("Data/Zulassungen/Zulassungen_alle_Fahrzeuge.csv")

# set encoding standard for parsing columns with geographic values
locale = locale(encoding = 'ISO-8859-1')

# Import with readr, coerce col_types
tier1 <- read_csv2("Data/Geodaten/Tier1_Werke_2017-07-11_v1.2_TrR.csv",
                   locale = locale, col_types = "ccnnn")
tier2 <- read_csv2("Data/Geodaten/Tier2_Werke_2017-07-11_v1.2_TrR.csv",
                   locale = locale, col_types = "ccnnn")
oem <- read_csv2("Data/Geodaten/OEM_Werke_2017-07-04_TrR.csv",
                   locale = locale, col_types = "ccnnn")
gemeinden <- read_csv2("Data/Geodaten/Geodaten_Gemeinden_v1.2_2017-08-22_TrR.csv", 
                   locale = locale, col_types = "__ccnn")

Datenaufbereitung

Nach dem Importieren galt es die Daten aufzubereiten und in ein analysierbares Format zu bringen. Dazu wurde das “Tidy Data”-Prinzip mit Funktionen aus dem tidyverse-Package angewandt. Ein Datensatz gilt demnach als “tidy”, wenn folgende Bedigungen erfüllt sind:

Außerdem mussten die Datensätze von unnützlichen Einträgen wie Zeilen oder Spalten, die keine Daten enthielten, bereinigt werden.

Um einen Eindruck zur Datenstruktur zu erhalten, wurden die Funktionen str() (für die allgemeine Struktur und die Klassen der Variablen) sowie head() und tail() (für Einträge am Anfang und Ende des Datensatzes) angewandt.

Einzeilteile

Alle Einzelteil-Datensätze hatten jeweils zwei überflüssige Spalten mit den Variablen X1 und X1_1, deren Werte lediglich eine fortlaufende Nummerierung der Spalten darstellten. Diese Spalten wurden entsprechend entfernt. Die Spaltennamen für die IDs wurden als “Einzelteil_ID” für den späteren Join vereinheitlicht.

Die Einzelteil-Datensätze lagen außerdem in zwei unterschiedlichen Strukturen vor. In den Tabellen “T11”, T14“,”T19" und “T20” waren die Werte für das jeweilige Produktionsdatum über zwei Spalten verteilt: In der Spalte “Produktionsdatum_01011970_origin” als Anzahl von Tagen im integer-Format basierend auf dem Wert “01-01-1970” in der “origin”-Spalte im Date-Format. Die integer-Werte mussten deshalb entsprechend mit der mutate() Funktion auf das origin-Datum addiert, in einer neuen Variable “Produktionsdatum” gespeichert und die übrigen Spalten gelöscht werden.

## Produktionsdatum_Origin_01011970 (in T11, T14, T19, T20) is an integer representing days since origin date
# Transform origin column into Class Date, use mutate to add Produktionsdatum_Origin_01011970 values to origin values
t11 <- t11 %>% 
  mutate(Produktionsdatum = as.Date(origin, format = "%d-%m-%Y") + Produktionsdatum_Origin_01011970) %>% 
  # Remove unnecessary columns and rename ID column to get common column names for all t* datasets
  select(-X1, -X1_1, -origin, -Produktionsdatum_Origin_01011970, ID_Einzelteil = ID_T11) %>%
  # add column "Einzelteil" with unique value
  mutate(Einzelteil="11")
t14 <- t14 %>% 
  mutate(Produktionsdatum = as.Date(origin, format = "%d-%m-%Y") + Produktionsdatum_Origin_01011970) %>%
  select(-X1, -X1_1, -origin, -Produktionsdatum_Origin_01011970, ID_Einzelteil = ID_T14) %>%
  mutate(Einzelteil="14")
t19 <- t19 %>% 
  mutate(Produktionsdatum = as.Date(origin, format = "%d-%m-%Y") + Produktionsdatum_Origin_01011970) %>%
  select(-X1, -X1_1, -origin, -Produktionsdatum_Origin_01011970, ID_Einzelteil = ID_T19) %>%
  mutate(Einzelteil="19")
t20 <- t20 %>% 
  mutate(Produktionsdatum = as.Date(origin, format = "%d-%m-%Y") + Produktionsdatum_Origin_01011970) %>%
  select(-X1, -X1_1, -origin, -Produktionsdatum_Origin_01011970, ID_Einzelteil = ID_T20) %>%
  mutate(Einzelteil="20")

In den Datensätzen “T15” und “T16” waren die wesentlichen Variablen jeweils auf zwei (T15) bzw. drei (T16) Spalten (z.B. ID_T16.x, ID_T16.y und ID_T16) verteilt, was eventuell auf einen nicht ordnungsgemäßen Join zurückzuführen ist. Folglich beinhaltete die Tabelle viele fehlende Werte. Nach diesen wurde der Datensatz respektiv gefiltert per Selektierung der Spaltenzahlen in zwei bzw. drei Parts aufgespaltet. Hinterher wurden die Spaltennamen vereinheitlicht und die zwei bzw. drei Parts mittel bind_rows() in jeweils einen Datensatz zusammengeführt.

Außerdem haben alle Einzelteil-Datensätze jeweils eine zusätzliche Spalte “Einzelteil” mit dem Anfangswert ihrer ID-Nummer bekommen, um die Richtigkeit des ID-Werts später prüfen zu können (s. Join-Abschnitt).

# remove unnecessary columns in t15 and t16
t15 <- select(t15, -X1, -X1_1)
t16 <- select(t16, -X1, -X1_1)

# variables in t15 / t16 tables are distributed over two / three parts, split table into two / three tables
# filter for missing values and select relevant columns in order to split the table
t15_part1 <- filter(t15, !is.na(ID_T15.x)) %>% 
  select(c(-8:-14))
t15_part2 <- filter(t15, !is.na(ID_T15.y)) %>%
  select(c(-1:-7))

t16_part1 <- filter(t16, !is.na(ID_T16.x)) %>% 
  select(c(-8:-21))
t16_part2 <- filter(t16, !is.na(ID_T16.y)) %>% 
  select(c(-1:-7, -15:-21)) 
t16_part3 <- filter(t16, !is.na(ID_T16)) %>% 
  select(c(-1:-14)) 

# Rename column names to the same names for all parts of T15 and T16 data set respectively
colnames(t15_part2) <- c("ID_Einzelteil", "Produktionsdatum", "Herstellernummer","Werksnummer", "Fehlerhaft", "Fehlerhaft_Datum", "Fehlerhaft_Fahrleistung" )
colnames(t15_part1) <- colnames(t15_part2)

colnames(t16_part3) <- c("ID_Einzelteil", "Produktionsdatum", "Herstellernummer","Werksnummer", "Fehlerhaft", "Fehlerhaft_Datum", "Fehlerhaft_Fahrleistung" )
colnames(t16_part2) <- colnames(t16_part3)
colnames(t16_part1) <- colnames(t16_part3)

# Bind the two (or three) tables each into one dataset, respectively
t15 <- bind_rows(t15_part1, t15_part2) %>% 
  # Move column Produktionsdatum to the last position in order to get the same structure for all Einzelteil datasets
  select(-Produktionsdatum, Produktionsdatum) %>%
  mutate(Einzelteil="15")

t16 <- bind_rows(t16_part1, t16_part2, t16_part3) %>%
  select(-Produktionsdatum, Produktionsdatum) %>%
  mutate(Einzelteil="16")

# delete unused objects
t15_part1 <- ""
t15_part2 <- ""
t16_part1 <- ""
t16_part2 <- ""
t16_part3 <- ""
gc()

Relationstabelle: Bestandteile_Komponenten

Die beiden relevanten Komponenten “K2LE1” und “K2LE2” stehen jeweils in Relation zu drei unterschiedlichen Einzelteilen, deren IDs in den “Bestandteile_Komponente”-Datensätzen jeweils als eine Spalte aufgeführt sind. Um alle Einzelteile in einer Spalte und damit eine Variable “ID_Einzelteil” unterzubringen und den späteren Join zu erleichtern, wurden die Datensätze jeweils nach den drei Einzelteilen aufgeteilt und deren Zeilen mittels bind_rows() zu einem Datensatz verbunden. Analog zu den Einzelteil-Datensätzen wurden unnütze Spalten entfernt und die ID in “ID_Komponente” unbenannt.

# tidy and transform: Rename "ID_K2LE*" columns to ID_Komponente in order to match with komponenten dataset and remove unnecessary columns
bestandteile_k2le1 <- bestandteile_k2le1 %>%
  select(-X1, ID_Komponente = ID_K2LE1)
bestandteile_k2le2 <- bestandteile_k2le2 %>%
  select(-X1, ID_Komponente = ID_K2LE2)

# Each "Komponente" (k2le1 & k2le2) consists of three parts (t11, t14, t15 & t16, t19, t20): Split into three different tables resepectively and rename "ID_T*" columns into "ID_Einzelteil"
b_k2le1_part1 <- bestandteile_k2le1[, -2:-3] %>%
  rename(ID_Einzelteil = ID_T11)
b_k2le1_part2 <- bestandteile_k2le1[, c(-1,-3)] %>%
  rename(ID_Einzelteil = ID_T14)
b_k2le1_part3 <- bestandteile_k2le1[, -1:-2] %>%
  rename(ID_Einzelteil = ID_T15)

b_k2le2_part1 <- bestandteile_k2le2[, -2:-3]%>%
  rename(ID_Einzelteil = ID_T16)
b_k2le2_part2 <- bestandteile_k2le2[, c(-1,-3)] %>%
  rename(ID_Einzelteil = ID_T19)
b_k2le2_part3 <- bestandteile_k2le2[, -1:-2] %>%
  rename(ID_Einzelteil = ID_T20)

# bind the split tables into one table respectively
bestandteile_k2le1_clean <- bind_rows(b_k2le1_part1, b_k2le1_part2, b_k2le1_part3)
bestandteile_k2le2_clean <- bind_rows(b_k2le2_part1, b_k2le2_part2, b_k2le2_part3)

# remove unused objects
b_k2le1_part1 <- ""
b_k2le1_part2 <- ""
b_k2le1_part3 <- ""
b_k2le2_part1 <- ""
b_k2le2_part2 <- ""
b_k2le2_part3 <- ""
gc()

Komponenten

So wie bei den Einzelteil-Datensätzen “T15” und “T16” waren die Variablen im Komponenten-Datensatz “K2LE1” auf zwei Spalten aufgeteilt. Entsprechend wurde der Datensatz aufgeteilt und per bind_rows() zusammengeführt. In “K2LE2” war das Produktionsdatum ebenfalls auf zwei Variablen aufgeteilt und musste per mutate() zusammenaddiert werden.

Überflüssige Spalten (X1 und X2) wurden gelöscht und eine Vergleichswert für die ID in der Variable “Komponente” für die spätere Prüfung der ID-Werte hinzugefügt.

# remove irrevelant columns
komponente_k2le1 <- select(komponente_k2le1, -X1, -X2)

# variables in komponente_k2le1 are split into two parts, split table in two (x and y)
komponente_k2le1.x <- komponente_k2le1 %>%
  select(1:7) %>%
  # filter out those where ID is na
  filter(!is.na(ID_Sitze.x)) %>%
  rename(
    ID_Komponente = ID_Sitze.x,
    Produktionsdatum = Produktionsdatum.x,
    Herstellernummer = Herstellernummer.x,
    Werksnummer = Werksnummer.x,
    Fehlerhaft = Fehlerhaft.x,
    Fehlerhaft_Datum = Fehlerhaft_Datum.x,
    Fehlerhaft_Fahrleistung = Fehlerhaft_Fahrleistung.x
  )

komponente_k2le1.y <- komponente_k2le1 %>%
  select(8:14) %>%
  # filter out those where ID is missing
  filter(!is.na(ID_Sitze.y)) %>%
  rename(
    ID_Komponente = ID_Sitze.y,
    Produktionsdatum = Produktionsdatum.y,
    Herstellernummer = Herstellernummer.y,
    Werksnummer = Werksnummer.y,
    Fehlerhaft = Fehlerhaft.y,
    Fehlerhaft_Datum = Fehlerhaft_Datum.y,
    Fehlerhaft_Fahrleistung = Fehlerhaft_Fahrleistung.y
  )

# bind the two tables (x and y) together again the right way,  add new colum with unique values
komponente_k2le1 <- bind_rows(komponente_k2le1.x, komponente_k2le1.y) %>%
  mutate(Komponente = "K2LE1")

# remove unused objects and free memory
komponente_k2le1.x <- ""
komponente_k2le1.y <- ""
tx <- ""
tx2 <- ""
tx3 <- ""
out <- ""
myFile <- ""
gc()

# clean komponente_k2le2
komponente_k2le2 <- komponente_k2le2 %>%
  # format origin in date format and create Produktionsdatum, remove irrelevant columns, rename ID_Sitze column and add Komponente column with unique value
  mutate(origin = as.Date(origin, format = "%d-%m-%Y"), Produktionsdatum = as.Date(Produktionsdatum_Origin_01011970, origin = origin)) %>%
  select(-X1, ID_Komponente = ID_Sitze, -X2, -origin, -Produktionsdatum_Origin_01011970) %>%
  mutate(Komponente = "K2LE2")

Relationstabelle: Bestandteile_Fahrzeuge

In den “Bestandteile_Fahrzeug”-Datensätzen mussten lediglich die Variablen der Spalten für den späteren Join umbenannt werden.

Fahrzeuge

In den “Fahrzeug”-Datensätzen wurden überflüssige Spalten (X1 und X1_1), welche wie bei den anderen Daten lediglich Zeilennummern darstellten, entfernt und der Anfangswert der ID_Fahrzeug in der Spalte “Fahrzeugtyp” für den späteren Vergleich gespeichert.

Analog zu “K2LE2” war das Produktionsdatum in den Datensätzen der Fahrzeuge “Typ21” und “Typ22” über zwei Spalten verteilt und musste entsprechend zusammenaddiert werden.

# select and rename columns
bestandteile_oem1_typ11 <- bestandteile_oem1_typ11 %>%
  select(ID_Komponente = ID_Sitze, ID_Fahrzeug)
bestandteile_oem1_typ12 <- bestandteile_oem1_typ12 %>%
  select(ID_Komponente = ID_Sitze, ID_Fahrzeug)
bestandteile_oem2_typ21 <- bestandteile_oem2_typ21 %>%
  select(ID_Komponente = ID_Sitze, ID_Fahrzeug)
bestandteile_oem2_typ22 <- bestandteile_oem2_typ22 %>%
  select(ID_Komponente = ID_Sitze, ID_Fahrzeug)

# deselect irrelevant columns and mutate a Fahrzeugtyp column for a later check
# transform origin in date format and create Produktionsdatum
fahrzeug_oem1_typ11 <- fahrzeug_oem1_typ11 %>%
  select(-X1, -X1_1) %>%
  mutate(Fahrzeugtyp = 11)
fahrzeug_oem1_typ12 <- fahrzeug_oem1_typ12 %>%
  select(-X1, -X1_1) %>%
  mutate(Fahrzeugtyp = 12)
fahrzeug_oem2_typ21 <- fahrzeug_oem2_typ21 %>%
  mutate(origin = (as.Date(origin, format = "%d-%m-%Y")), Produktionsdatum = as.Date(Produktionsdatum_Origin_01011970, origin = origin)) %>%
  select(-X1, -X1_1, -origin, -Produktionsdatum_Origin_01011970) %>%
  mutate(Fahrzeugtyp = 21)
fahrzeug_oem2_typ22 <- fahrzeug_oem2_typ22 %>%
  mutate(origin = (as.Date(origin, format = "%d-%m-%Y")), Produktionsdatum = as.Date(Produktionsdatum_Origin_01011970, origin = origin)) %>%
  select(-X1, -X1_1, -origin, -Produktionsdatum_Origin_01011970) %>%
  mutate(Fahrzeugtyp = 22)

Zulassungen

In den “Zulassungen”-Daten wurden Beobachtungen mit fehlenden ID-Werten gelöscht, da diese nicht verwertet werden können, und Variablen umbenannt, um Joins zu erleichtern.

Geodaten

Die Geodaten bringen besondere Anforderungen für die spätere Darstellung in der Shiny-App mit sich:

  • die Koordinaten sollten vom Typ double sein, um sie anschließend in der App von leaflet in der Kartendarstellung anzuzeigen
  • Postleitzahlen sollten 5 Zeichen und alle Gemeinden Deutschlands beinhalten

Nach dem Import wurden die Umlaute korrigiert und Postleitzahlen in PLZ umbenannt. Die Postleitzahlen wurden als nächstes normiert, indem eine anführende “0”-Ziffer eingefügt wurde, wenn die PLZ weniger als 5 Ziffern hatte.

Nach der Erstellung des finalen Datensatzes wurde festgestellt, dass Daten für die Gemeinde “Seeg” im “Gemeinden”-Datensatz nicht enthalten waren, der Ort jedoch in den “Zulassungen”-Daten aufgeführt war. Der Ort “Seeg” musste demnach mitsamt PLZ und Koordinaten als weitere Beobachtung bzw. Zeile in den Gemeinden hinzugefügt werden.

Zuletzt wurden leere Spalten gelöscht, die Koordinaten normiert und Spaltennamen geändert. Hier bestanden insbesondere Unregelmäßigkeiten im Format und der Ziffernanzahl der Längen- und Breitengrade. Dazu wurde die Funktion addZerosUntil8Digits() geschrieben, die den Koordinaten “0”-Ziffern anfügt, bis 8 Stellen erreicht sind, um alle Koordinaten später auf eine zweistellige Zahl mit Nachkommastellen dividieren zu können. Dabei ergab sich jedoch das Problem, dass manche Längengrade vor dem Komma einstellig sein müssen, da Deutschland zwischen ~6 und ~16°E liegt. Eine weitere Funktion chopBigLongitudes() diviert den Längegrad deshalb ein weiteres Mal durch 10, wenn er den Wert 20 vor dem Komma überschreitet.

# Clean ID from NA and rename columns in zulassungen
zulassungen <- zulassungen %>%
  drop_na(IDNummer) %>%
  select(ID_Fahrzeug = IDNummer, Gemeinde = Gemeinden, Zulassungsdatum = Zulassung)

# Change ae to ä in Längengrad
colnames(tier1)[5] <- "Längengrad"
colnames(tier2)[5] <- "Längengrad"
colnames(oem)[5] <- "Längengrad"
colnames(gemeinden)[3] <- "Längengrad"
# Change Postleitzahl to PLZ
colnames(gemeinden)[1] <- "PLZ"

# Function that add leading zero to PLZ
addLeadingZero <- function(char) {
  ifelse(nchar(char) < 5, char <- paste0("0",char), char)
    return(char) }

# Add leading zero to PLZ if missing
gemeinden <- gemeinden %>%
  rowwise() %>%
  mutate(PLZ = addLeadingZero(PLZ))

# After final join in the end missing coordinates and PLZ for location "Seeg" were detected. Data was missing in gemeinden dataset: Add missing data as row to gemeinden dataframe
gemeinden_clean <- rbind(gemeinden, list("87637", "SEEG", 10.610416, 47.654222))
str(gemeinden_clean)

# Function that add zeros at the end of the number until it has 8 digits
addZerosUntil8Digits <- function(num) {
  for (i in 1:7) { ifelse(nchar(trunc(num)) < 8, num <- num*10, num) }
    return(num) }

# After adding zeros, all coordinates have the same amount of digits. However, Germany ranges between ~6 and ~16°E in longitude (one or two digit)
# Function that chops one decimal in Längengrad values if it's outside Germany's proximity
chopBigLongitudes <- function(x){
  ifelse(x > 20, x <- x/10, x)
}

# Clean: omit NAs, standardize geodata and change column names
tier1_clean <- tier1  %>%
  na.omit(tier1)  %>%
  rowwise() %>%
  mutate(Breitengrad = addZerosUntil8Digits(Breitengrad))  %>%
  mutate(Längengrad = addZerosUntil8Digits(Längengrad)) %>%
  # divide coordinates by 1000000 down to two digit values with decimals
  mutate(Breitengrad = Breitengrad/1000000) %>%
  mutate(Längengrad = Längengrad/1000000) %>%
  # use chopping function in case longitude is out of range and get one-digit longitude
  mutate(Längengrad = chopBigLongitudes(Längengrad)) %>%
  rename(Werksnummer = Werk, Ort = ORT)

tier2_clean <- tier2  %>%
  na.omit(tier2)  %>%
  rowwise() %>%
  mutate(PLZ = addLeadingZero(PLZ) ) %>%
  mutate(Breitengrad = addZerosUntil8Digits(Breitengrad))  %>%
  mutate(Längengrad = addZerosUntil8Digits(Längengrad)) %>%
  mutate(Breitengrad = Breitengrad/1000000) %>%
  mutate(Längengrad = Längengrad/1000000) %>%
  mutate(Längengrad = chopBigLongitudes(Längengrad)) %>%
  rename(Werksnummer = Werk, Ort = ORT)

Erstellen des finalen Datensatzes für die Auswertung

Zu Erstellung des finalen Datensatzes mussten die Zusammenhänge zwischen den einzelnen Datensätze für die Auswertung relevanten Daten bekannt sein.

Die relationalen Tabellen verknüpfen die Einzelteile, Komponenten und Fahrzeug über ihre jeweilige ID-Spalten. Letztere sind wiederum über die jeweilige Werksnummer mit den entsprechenden Geodaten verknüpft. Der Datensatz “Zulassungen” stellt das Bindeglied zwischen den Fahrzeugen und den Gemeinden dar.

Gegenstand der Analyse sind die Fahrzeuge, welche die Komponente “Ledersitze” (K2LE1 oder K2LE2) des Herstellers “111” sowie deren Einzelteile, die als fehlerhaft markiert sind, enthalten. Für die Auswertung und grafische Darstellung in der Shiny-App sind demnach entsprechende IDs zu den Fahrzeugen, Komponenten und Einzeiteilen, die Werksnummer und Koordinaten zu den Einzelteilen und Komponenten, Informationen, ob entsprechende Bauteile als fehlerhaft markiert sind oder nicht, sowie alle Zulassungsdaten für die Fahrzeuge relevant. Diese Informationen müssen für den finalen Datensatz anschließend auf den Komponentenhersteller “111” und Fahrzeuge, die entweder fehlerhafte Einzelteile oder Komponenten enthalten, gefiltert werden.

Zusammenführen der einzelnen Datensätze nach Kategorie

Zunächst mussten die einzelnen Datensätze der Kategorien “Einzelteil”, “Komponente” und “Fahrzeug” sowie deren relationalen Tabellen “Bestandteile_Komponente” und “Bestandteile_Fahrzeug” jeweils in einem Datensatz zusammengeführt werden. Da die Spaltennamen und -anzahlen in vorangegangen Schritten entsprechend vereinheitlicht wurden, konnte jeweils die Funktion bind_rows() angewandt werden.

Weiterhin wurde der Datensatz “Bestandteile_Fahrzeuge”, welche sich aus unterschiedlichen Komponenten zusammensetzen, auf Komponenten-IDs beginnend mit “K2LE” gefiltert, damit nur noch die zu untersuchenden Ledersitze und ihre entsprechend zugeordneten Fahrzeuge angezeigt.

Zur Überprüfung der ID-Spalten in den Einzelteil-, Komponente- und Fahrzeugdatensätzen wurden diese jeweils in ihre Bestandteile, “Herstellernummer”, “Werksnummer” und Typnummer (“Einzelteil”, “Komponente” und “Fahrzeugtyp”) aufgetrennt und mit den Werten der jeweils korrespondierenden Spalte nach Übereinstimmung verglichen und das Ergebnis als logischer Wert in der Spalte "id_richtig_*" gespeichert. Hierbei wurden keine Fehler detektiert, sodass die nun überflüssigen, dem Vergleich dienenden Spalten entfernt werden konnten.

Die relationalen Tabellen wurden nach dem Join zusätzlich nach fehlenden Werten untersucht, wobei keine aufgetreten sind.

# use bind_rows(), seperate values in ID_Einzelteil column and test if it matches with the values of other columns
all_einzelteile <- bind_rows(t11, t14, t15, t16, t19, t20) %>%
  mutate(id_richtig_einzel =
           isTRUE(
              #check if ID-Herstellernr and Herstellernr match
              strsplit(ID_Einzelteil, split = "-", fixed = TRUE)[[1]][2] == Herstellernummer && 
              #check if ID-Werksnr and Werksnr match
              strsplit(ID_Einzelteil, split = "-", fixed = TRUE)[[1]][3] == Werksnummer &&
              #check if Herstellernr and Herstellernr in Werksnr match
              Herstellernummer == as.numeric(substring(Werksnummer, 1, nchar(Herstellernummer))) &&
              #check if ID-Einzelteil and Einzelteil match
              strsplit(ID_Einzelteil, split = "-", fixed = TRUE)[[1]][1] == Einzelteil
            )
  )

# if all IDs are correct, delete redundant columns
if(all(all_einzelteile$id_richtig_einzel == TRUE)) {
  all_einzelteile <- all_einzelteile %>%
    select(-id_richtig_einzel, -Einzelteil)
}

# relation table for all Komponenten and Einzelteile
bestandteile_komponenten <- bind_rows(bestandteile_k2le1_clean, bestandteile_k2le2_clean)

# check if any values are missing in the joined dataset
if(any(is.na(bestandteile_komponenten))==TRUE) {
   filter_all(bestandteile_komponenten, any_vars(is.na(.)))
}

# bind tables into one and check if IDs are correct
komponenten <- bind_rows(komponente_k2le1, komponente_k2le2) %>%
   mutate(id_richtig_komp =
           isTRUE(
              #check if ID-Herstellernr and Herstellernr match
              strsplit(ID_Komponente, split = "-", fixed = TRUE)[[1]][2] == Herstellernummer && 
              #check if ID-Werksnr and Werksnr match
              strsplit(ID_Komponente, split = "-", fixed = TRUE)[[1]][3] == Werksnummer &&
              #check if Herstellernr and Herstellernr in Werksnr match
              Herstellernummer == as.numeric(substring(Werksnummer, 1, nchar(Herstellernummer))) &&
              #check if ID-Komponente and Komponente match
              strsplit(ID_Komponente, split = "-", fixed = TRUE)[[1]][1] == Komponente
            )
  )

# if all IDs are correct, delete redundant columns
if(all(komponenten$id_richtig_komp == TRUE)){
  komponenten <- komponenten %>%
    select(-id_richtig_komp, -Komponente)
}

# bind Bestandteile together in one table and check if IDs correct
fahrzeug_bestandteile <- bind_rows(bestandteile_oem1_typ11, bestandteile_oem1_typ12) %>%
  bind_rows(bestandteile_oem2_typ21) %>%
  bind_rows(bestandteile_oem2_typ22) %>%
  filter(substring(ID_Komponente, 1, 4) == "K2LE") %>%
  mutate(id_richtig_fahrz_bestandteile = isTRUE(
      #check if Herstellernr and number in Werksnummer match in ID_Komponente
      substring(ID_Komponente, 7, 9) == substring(ID_Komponente, 11, 13) &&
      #check if Herstellernr and number in Werksnummer match in ID_Fahrzeuge
      substring(ID_Fahrzeug, 4, 4) == substring(ID_Fahrzeug, 6, 6)
  ))

# if all IDs are correct, deselect redundant columns
if(all(fahrzeug_bestandteile$id_richtig_fahrz_bestandteile == TRUE)){
  fahrzeug_bestandteile <- fahrzeug_bestandteile %>%
    select(-id_richtig_fahrz_bestandteile)
}

# check if any values are missing in the joined dataset
if(any(is.na(fahrzeug_bestandteile))==TRUE) {
   filter_all(fahrzeug_bestandteile, any_vars(is.na(.)))
}

# bind Fahrzeug data sets by row together in one table, check if IDs are correct
all_fahrzeuge <- bind_rows(fahrzeug_oem1_typ11, fahrzeug_oem1_typ12) %>%
  bind_rows(fahrzeug_oem2_typ21) %>%
  bind_rows(fahrzeug_oem2_typ22) %>%
  mutate(id_richtig_fahrzeuge = isTRUE(
              #check if ID-Fahrzeug and Herstellernr match
              substring(ID_Fahrzeug, 4, 4) == Herstellernummer && 
              #check if ID-Werksnr and Werksnr match
              substring(ID_Fahrzeug, 6, 7) == Werksnummer &&
              #check if Herstellernr and number in Werksnr match
              Herstellernummer == as.numeric(substring(Werksnummer, 1, nchar(Herstellernummer))) &&
              # check if Fahrzeug has correct type
              substring(ID_Fahrzeug, 1, 2) == Fahrzeugtyp
  ))

# if all IDs are correct, deselect redundant columns
if(all(all_fahrzeuge$id_richtig_fahrzeuge == TRUE)){
  all_fahrzeuge <- all_fahrzeuge %>%
    select(-id_richtig_fahrzeuge, -Fahrzeugtyp)
}

Joining mit relationalen Datensätzen und Transforming

Die “Einzelteil”-Datensätze sind mit den Komponenten über die relationalen Tabellen “Bestandteile_Komponenten” durch die Schlüsselvariable “ID_Einzelteile” verknüpft. Analog dazu sind die Komponenten mit den Fahrzeugen über “Fahrzeug_Bestandteile” durch die Variable “ID_Fahrzeug” verknüpft.

Zur Zusammenführung wurde zunächst jeweils ein right_join() auf Einzelteile und Relationstabelle und Fahrzeuge mit Relationstabelle über die entsprechenden Schlüsselvariablen angewandt, um respektiv alle relevanten Komponenten (Ledesitze) und alle Fahrzeuge, die diese enthalten, zu behalten und die dazugehörigen Informationen zu Einzelteilen und Fahrzeugen zu erhalten. Zusätzlich wurden die Werte der Spalte “Produktionsdatum” und “Fehlerhaft_Datum” in ein einheitliches Date-Format transformiert.

# right_join to keep all relevant Komponenten (leather seats) and the related Einzelteile, transform dates in correct class and format
einzelteile_rel <- right_join(all_einzelteile, bestandteile_komponenten, by = "ID_Einzelteil") %>%
  mutate(Produktionsdatum = as.Date(Produktionsdatum, format = "%Y-%m-%d"),
         Fehlerhaft_Datum = as.Date(Fehlerhaft_Datum, format = "%Y-%m-%d"))

# right_join to keep all fahrzeuge with leather seats, transform dates in correct format
fahrzeuge_rel <- right_join(all_fahrzeuge, fahrzeug_bestandteile, by = "ID_Fahrzeug") %>%
  mutate(Produktionsdatum = as.Date(Produktionsdatum, format = "%Y-%m-%d"),
         Fehlerhaft_Datum = as.Date(Fehlerhaft_Datum, format = "%Y-%m-%d"))

Joining der Datensätze mit Geodaten und Transforming

Im nächsten Schritt galt es die Datensätze, die Werksinformationen (Einzelteile, Komponenten und Fahrzeuge) enthalten, mit den entsprechenden Geodaten zu verbinden.

Für die Einzelteile und Komponenten wurde jeweils ein left_join() über die Schlüssevariable “Werksnummer” angewandt, um nur geografische Informationen zu Werken relevanter Bauteile beizubehalten. Da aus den gejointen Datensätzen für die spätere Analyse nur noch die ID, Werksnummer und die geografischen Koordinaten relevant sind, wurden die restlichen Spalten entfernt. Für die Komponenten war vorübergehend noch die Herstellernummer relevant, um diese später für unseren Anwendungsfall filtern zu können.

Die Fahrzeug-Datensätze wurden ebenfalls per left_join() mit den Zulassungs- und Gemeinde-Daten, welche geografische Informationen zu den Zulassungsorten enthalten, über die Schlüsselvariable “ID_Fahrzeug” zusammengeführt, um alle bisher gefilterten Fahrzeuge und die dazu relevanten Zulassungsinformationen sowie Gemeindaten zu behalten. Informationen zu den OEM-Werken sind für die Auswertung nicht relevant.

Bei der Suche nach fehlenden Werten wurden in den gejointen Fahrzeug-Zulassung-Gemeindedaten traten fehlende Gemeindedaten zu dem Zulassungsort “Seeg” auf, welche nachträglich im Gemeinden-Datensatz hinzugefügt wurden.

# Join einzelteile_rel with respective geodata to get coordinates of each manufactory ("Werk"), left join to keep all einzelteile data and only relevant coordinates
einzelteile_geo <- left_join(einzelteile_rel, tier2_clean, by = "Werksnummer") %>%
  # remove columns irrelevant for final dataset
  select(-PLZ, -Ort, -Fehlerhaft_Datum, -Fehlerhaft_Fahrleistung, -Herstellernummer, -Produktionsdatum)

# Join komponenten with respective geodata to get coordinates of each manufactory ("Werk"), left join to keep all komponenten data and only relevant coordinates
komponenten_geo <- left_join(komponenten, tier1_clean, by = "Werksnummer") %>%
  select(-PLZ, -Ort, -Fehlerhaft_Datum, -Fehlerhaft_Fahrleistung, -Produktionsdatum, Herstellernummer_Komponente = Herstellernummer)

# Join fahrzeuge with zulassungen and gemeinden_clean (containing coordinates), left joins to keep all fahrzeuge data and only relevant zulassungen and coordinates
fahrzeuge_geo <- left_join(fahrzeuge_rel, zulassungen, by = "ID_Fahrzeug") %>%
  left_join(., gemeinden_clean, by = "Gemeinde") %>%
  select(-Fehlerhaft_Datum, -Fehlerhaft_Fahrleistung, -Herstellernummer, -Fehlerhaft)

# check if any values are missing in the joined datasets
if(any(is.na(einzelteile_geo))==TRUE) {
  filter_all(einzelteile_geo, any_vars(is.na(.)))
}
if(any(is.na(komponenten_geo))==TRUE) {
  filter_all(komponenten_geo, any_vars(is.na(.)))
}
if(any(is.na(fahrzeuge_geo))==TRUE) {
  filter_all(fahrzeuge_geo, any_vars(is.na(.)))
  # the filtered dataset initially showed that coordinates and PLZ are missing for the location "Seeg". The PLZ is 87637, Längengrad = 10.612874 and Breitengrad = 47.654894. The entry "Seeg" was completely missing in the "gemeinden" dataset and the NA occured after joining with zulassen. The Data was added too gemeinden dataset
}

Finaler Datensatz

Im letzten Abschnitt zum finalen Datensatz wurden die Einzelteile-Geodaten, Komponenten-Geodaten und Fahrzeuge-Geodaten nacheinander mittels full_join() über die Schlüsselvariable “ID_Komponente” verbunden, um alle Daten um die relevanten Komponenten herum zu erhalten. Da die drei Datensätze teilweise dieselben Spaltennamen hatten, mussten sie mittels suffix = c("_Einzelteil“,”_Komponente") unterschieden werden. Entsprechende Spaltennamen für die Fahrzeugdaten wurden nachträglich mittels der Funktion rename() kenntlich gemacht.

Da wir nur Komponenten des Herstellers “111” auswerten, wurde der zusammengeführte Datensatz auf entsprechende Herstellernummer gefiltert. Laut Aufgabenstellung konnten entweder Komponenten oder Einzelteile als fehlerhaft markiert sein. Dabei galten Komponenten, die fehlerhafte Einzelteile enthalten, als ausgefallen, auch wie die Komponenten selbst nicht als fehlerhaft markiert sind. Deshalb wurde der Datensatz ebenfalls auf fehlerhafte Einzelteile und / oder fehlerhafte Komponenten mittels inklusivem OR-Operator gefiltert. Die Herstellernummer wurde hinterher nicht weiter benötigt und konnte entfernt werden.

Zwecks Übersichtlichkeit wurden die Spalten im finalen Datensatz neu sortiert.

# Final join: einzelteile_, komponenten_, and fahrzeuge_clean by ID_Komponente column
final_join <- full_join(einzelteile_geo, komponenten_geo, by = "ID_Komponente", suffix = c("_Einzelteil", "_Komponente")) %>%
  full_join(., fahrzeuge_geo, by ="ID_Komponente") %>%
  rename(Produktionsdatum_Fahrzeug = Produktionsdatum, Werksnummer_Fahrzeug = Werksnummer)

# filter for Herstellernummer_Komponente == 111 and Fehlerhaft == 1 to get faulty parts and components of seats produced by company "111"
final_filtered <- final_join %>%
  filter(Herstellernummer_Komponente == 111) %>%
  filter(Fehlerhaft_Einzelteil == 1 | Fehlerhaft_Komponente == 1) %>%
  select(-Herstellernummer_Komponente)

# reorder columns
final_order <- final_filtered[c(1:3,5,6,4,7:11,13,12,15,14,16,18,17)]

# final dataset
final_joined <- final_order

Der finale Datensatz beinhaltet nun alle Fahrzeuge mit fehlerhaften Komponenten (“Ledersitzen”) des Herstellers “111” bzw. in Komponenten des Herstellers “111” verbaute, fehlerhafte Einzelteile sowie alle für die Anaylse und Darstellung relavanten Daten zu Einzelteilen, Komponenten und Fahrzeugen. Der Datensatz ist wie folgt strukturiert:

Finaler Datensatz Teil 1 - Einzelteile
ID_Einzelteil Werksnummer_Einzelteil Fehlerhaft_Einzelteil Breitengrad_Einzelteil Längengrad_Einzelteil
11-213-2131-476113 2131 0 50.59254 8.631708
11-213-2131-476114 2131 0 50.59254 8.631708
11-215-2154-190073 2154 0 48.85681 10.104846
11-215-2154-190089 2154 0 48.85681 10.104846
11-215-2154-190164 2154 0 48.85681 10.104846
Finaler Datensatz Teil 2 - Komponenten (Ledersitze) des Herstellers ‘111’
ID_Komponente Werksnummer_Komponente Fehlerhaft_Komponente Breitengrad_Komponente Längengrad_Komponente
K2LE1-111-1111-2 1111 1 51.52031 7.480238
K2LE1-111-1111-3 1111 1 51.52031 7.480238
K2LE1-111-1111-5 1111 1 51.52031 7.480238
K2LE1-111-1111-9 1111 1 51.52031 7.480238
K2LE1-111-1111-15 1111 1 51.52031 7.480238
Finaler Datensatz Teil 3 - Fahrzeuge
ID_Fahrzeug Werksnummer_Fahrzeug Produktionsdatum_Fahrzeug Zulassungsdatum Gemeinde PLZ Breitengrad Längengrad
11-1-11-474913 11 2012-02-04 2012-05-02 MOETZINGEN 71159 48.53528 8.773708
12-1-12-163361 12 2012-02-05 2012-05-02 FORSTERN 85659 48.18337 11.977334
12-1-12-163487 12 2012-02-06 2012-05-04 BOCHUM 44787 51.48270 7.217177
11-1-11-474872 11 2012-02-04 2012-05-02 MUEHLHAUSEN 69242 49.24814 8.726913
11-1-12-313647 12 2012-02-03 2012-05-02 WIETMARSCHEN 49835 52.51958 7.132326

Die kritische Produktionsmenge an Fahrzeugen mit fehlerhaften Bauteilen (Komponenten und/oder Einzelteilen) lautet damit wie folgt:

Anzahl der defekten Fahrzeuge und Bauteile
Anzahl der Dateneinträge Fahrzeuge mit defekten Bauteilen Als defekt markierte Komponenten Als defekt markierte Einzelteile
322075 159989 76567 115293

Jeder Dateneintrag entspricht einem Einzelteil, dass defekt ist und/oder einer defekten Komponente und damit einem Fahrzeug zugeordnet ist. Zu beachten ist, dass jede Komponente einzigartig ist und genau einem Fahrzeug zugeordnet ist und umgekehrt. Jedes Einzelteil ist genau einer Komponente zugeordnet und eine Komponente hat genau drei (bzw. bis zu drei fehlerhafte) Einzelteile. Dabei ist zu erkennen, dass im finalen Datensatz nicht alle Komponenten als fehlerhaft markiert sind, was bedeutet, dass fehlerhafte Einzelteile verbaut sind, ohne dass die Komponente als fehlerhaft markiert wurde.

Folgende Tabelle stellt fehlerhafte Komponenten ohne als fehlerhaft markierte Einzelteile und fehlerhafte Einzelteile, die nicht als fehlerhaft markierten Komponenten zugeordnet sind, dar:

Exklusiv defekte Komponenten und Einzelteile
Exklusiv als defekt markierte Komponenten Exklusiv als defekt markierte Einzelteile
55811 92374

Weitere Zusammenhänge sind im Zuge der grafischen Analyse mittels Shiny-App zu ermitteln.

Datenauswertung mittels Shiny-Applikation

Shiny ist ein Werkzeug aus der R- und RStudio-Umgebung zur interaktiven Präsentation von Analyseergebnissen. Es erlaubt die Erstellung von interaktiven Web-Applikationen, welche durch die Ausgabe im HTML-Format u.a. online oder im firmeneigenen Netzwerk verteilt werden können. Durch die Bedienung vorgefertigter Widgets und die automatische Konvertierung in HTML-Format werden zwar grundsätzlich keine Vorkenntnisse zu HTML oder JavaScript benötigt, die Shiny-App ist jedoch beliebig mit CSS, HTML und JavaScript erweiterbar.

Shiny-Applikationen setzen sich grundsätzlich aus vier Teilen zusammen:

Im Kern nimmt das UI Inputs des Nutzers auf und gibt diese an den Server, welcher daraus reaktive Outputs erzeugt, weiter. Die Ergebnisse werden dann wiederum dem Nutzer im UI dargestellt.

Anforderungen an die fallbezogene Shiny-App

Die Visualisierung der Datenauswertung in der Shiny-App soll folgende Bestandteile haben:

  • einen Graphen, der den zeitlichen Zulassungsverlauf der betroffenen Fahrzeuge in den Gemeinden darstellt
  • eine Heatmapdarstellung zur Visualisierung von Schadensschwerpunkten
  • Popups in der Kartendarstellung, die Informationen zu den zugelassenen Fahrzeugen enthalten
  • der Lieferweg (als Luftlinie) für ein betroffenes Bauteil, welches vom Benutzer ausgewählt werden kann
  • ein Suchfeld, das Fahrzeughaltern ermöglicht, anhand der Fahrzeug-ID zu überprüfen, ob ihr Auto betroffen ist
  • den vollständigen zugrundeliegenden Datensatz als Tabelle mit den notwendigen Attributen

Zusätzlich soll die Auswahl und Ausgabe eines betroffenen Fahrzeugs für den Fahrzeughalter von den restlichen Informationen, die dem Fahrzeughersteller zur Verfügung stehen, aus Datenschutzgründen isoliert werden.

User Interface

Grundsätzlich gliedert sich das User-Interface in zwei Bestandteile, welche über Tabs voneinander getrennt sind:

  • ein Interface für Fahrzeughalter
  • ein Interface für Fahrzeughersteller

Die Tabs dienen der anwenderspezifischen Isolierung der Datenauswertung.

Das UI für Fahrzeughalter beinhaltet lediglich die Suche nach einzelnen, von defekten Ledersitzes des Herstellers “111” betroffenen Fahrzeugen.

Das UI für Fahrzeughersteller ist hingegen umfangreicher und stellt sämtliche Daten zu allen Fahrzeugen, die von defekten Ledersitzen des Herstellers “111” betroffen sind, und deren Analyse dar. Es beinhaltet die folgenden vier Teilbereiche:

  • den Graphen für den zeitlichen Zulassungsverlauf der betroffenen Fahrzeuge
  • Auswahlfilter für den Zulassungszeitraum, die Gemeinden und Bauteile
  • die interaktive Kartendarstellung mit Schadenschwerpunkten als Heatmap, geclusterten Markierungspunkten für die zugelassenen Fahrzeugen sowie Lieferketten von den Herstellern zum Zulassungsort
  • den vollständigen zugrundeliegenden Datensatz mit Filterfunktion

Die einzelnen Bestandteile des UI werden in folgenden Abschnitten näher beschrieben.

Suche nach betroffenen Fahrzeugen für Fahrzeughalter

Mittels Eingabe der Fahrzeug-ID über das Suchfeld kann der Fahrzeughalter ermitteln, ob das Fahrzeug von defekten Ledersitzen der Herstellers “111” betroffen ist oder nicht.

Als Output wird dargestellt:

  • ob das Fahrzeug betroffen ist,
  • welche Bauteile betroffen sind,
  • in welchem Werk die Bauteile hergestellt wurden.

Bei fehlerhafter Eingabe der Fahrzeug-ID erscheint die Meldung “Fahrzeug-ID existiert nicht”. Die direkte Eingabe ohne Unterstützung von Shiny unterstützt hier den Datenschutz der anderen Autobesitzer.

Fahrzeugsuche Standardansicht

Fahrzeugsuche Standardansicht

Die Fahrzeug-ID ist nicht enthalten: Fahrzeug ist nicht betroffen

Die Fahrzeug-ID ist nicht enthalten: Fahrzeug ist nicht betroffen

Ausgabe, wenn Fahrzeug betroffen ist

Ausgabe, wenn Fahrzeug betroffen ist

Graph für den zeitlichen Zulassungsverlauf der betroffenen Fahrzeuge

Der Graph stellt dem Fahrzeughersteller den zeitlichen Zulassungsverlauf der betroffenen Fahrzeuge in Form eines Barplots dar. Der Barplot repräsentiert die Häufigkeitsverteilung fehlerhafter Fahrzeuge über den ausgewählten Zulassungszeitraum. Die Höhe der Balken stellt dabei die absolute Anzahl betroffener Anzahl je Zulassungsmonat dar. Die einzelnen Balken sind nach den vier Werken der Fahrzeughersteller (OEM) aufgegliedert, wobei jedes OEM-Werk durch eine feste Balkenfarbe repräsentiert wird.

Balkendiagramm Standardansicht

Balkendiagramm Standardansicht

Auswahl des Zulassungszeitraums

Der Zulassungszeitraum kann durch Festlegen von Start- und Enddaten über den Slider nach Bedarf bestimmt werden. Damit werden sowohl das Balkendiagramm für den Zulassungsverlauf als auch die Kartendarstellung, so gefiltert dass nur relevante Daten im ausgewählten Zeitraum ausgewertet und dargestellt werden.

In der Kartendarstellung werden durch die Auswahl des Zulassungszeitraums lediglich die Darstellung der Heatmap und Standortmarkierungen (“Cluster-Marker”) beeinflusst.

Standardmäßig ist der gesamte Zulassungszeitraum aller Fahrzeugdaten festgelegt.

Balkendiagramm nach Filtern des Zulassungszeitraums

Balkendiagramm nach Filtern des Zulassungszeitraums

Suchfilter für Gemeinden und Bauteile

In Form von DataTables wird separat nach Gemeinden und in betroffenen Fahrzeugen enthaltenten Bauteilen gesucht. Ein allgemeiner Suchfilter mit Texteingabe ermöglicht die Suche nach Postleitzahlen oder Gemeinden bzw. in der Bauteilsuche nach Einzelteil-, Komponenten- oder Fahrzeug-IDs. Eine Auswahl der Tabellenzeilen filtert die Daten, die ausgewertet und dargestellt werden. Hierbei ist eine Mehrfachauswahl möglich und die ausgewählten Zeilen werden farblich hervorgehoben. Die Abwahl der markierten Zeilen durch erneutes Anklicken hebt die Filter einzeln wieder auf.

Suchfilter Standardansicht

Suchfilter Standardansicht

Eine Auswahl einzelner Gemeinden filtert die ausgewerteten Daten sowohl für das Balkendiagramm als auch die Kartendarstellung. In der Kartendarstellung werden durch die Auswahl von Gemeinden lediglich die dargestellten Cluster-Marker für Fahrzeugstandort beeinflusst. In der Tabelle wird für die jeweiligen Gemeinden zusätzlich die Anzahl der zugelassenen betroffenen Fahrzeuge angezeigt.

Auswahl einer Gemeinde: Aachen

Auswahl einer Gemeinde: Aachen

Balkendiagramm nach Auswahl der Gemeinde Aachen

Balkendiagramm nach Auswahl der Gemeinde Aachen

Durch die Auswahl von Bauteilen wird die Darstellung der Lieferwege in der Kartendarstellung generiert. Die Auswahl der Bauteile filtert lediglich die für die Kartendarstellung ausgewerteten Daten und nicht die des Balkendiagramms, da eine grafische Darstellung der Fahrzeugzulassungen nach einzelnen Bauteilen keine gewinnbringende Information liefert und Bauteile je Fahrzeug über die Datentabellen ermittelt werden können.

Auswahl mehrerer Bauteile

Auswahl mehrerer Bauteile

Interaktive Karte mit Schadensschwerpunkten, betroffenen Fahrzeugen und Lieferwegen für Ersatzteile

Die “Open Street Map”, für deren Anzeige eine bestehende Internetverbindung benötigt wird, stellt die Deutschlandkarte mit drei verschiedenen Schichten, die aus den ausgewählten Daten generiert werden, dar. Die Schichten sind im einzelnen über ein Auswahlfeld aus- und einblendbar.

Kartendarstellung Standardansicht

Kartendarstellung Standardansicht

Karte mit Heatmap und Clustern gezoomt

Karte mit Heatmap und Clustern gezoomt

Die unterste Schicht bildet eine Heatmap mit den über das Bundesgebiet verteilten Schadensschwerpunkten ab. Eine rote Verfärbung stellt demnach eine räumlich hohe Konzentration von mit fehlerhaften Bauteilen betroffenen Fahrzeugen dar, während eine blaue Verfärbung eine räumlich niedrigere Konzentration bedeutet. In Regionen ohne Verfärbung befinden sich keine betroffenen Fahrzeuge.

Karte nur mit Heatmap

Karte nur mit Heatmap

In der mittleren Schicht liegen Cluster-Marker. Die einzelnen Marker repräsentieren jeweils ein betroffenes Fahrzeug und markieren den genauen Zulassungsort auf der Karte. Je nach Zoom-Ausrichtung der Karte, werden mehrere Marker in Clustern zusammengefasst. Popups aus den einzelnen Markern stellen nach dem Anklicken relevante Zulassungs- und Bauteilinformationen zu den Fahrzeugen dar.

Karte nur mit Clustern

Karte nur mit Clustern

Marker für zugelassene Fahrzeuge mit Popup-Informationen

Marker für zugelassene Fahrzeuge mit Popup-Informationen

Die oberste Schicht stellt nach Auswahl von Bauteilen bzw. betroffenen Fahrzeugen in den Datentabellen die Wege der Lieferketten als Luftlinie dar. Die Lieferkette wird durch die Wege von defekten Bauteilen zum Komponentenhersteller und vom Komponentenhersteller zum Fahrzeughalter definiert. Gebäude-Icons stellen zusätzlich die Einzeilteil- und Komponentenwerke dar und ein roter Geo-Icon den Zielort, d.h. den Zulassungsort des Fahrzeugs dar. Popups beim Anklicken des Werksstandorts geben wesentliche Informationen und Statistiken zu fehlerhaften Bauteilen aus in diesem Werk aus. Duch Mehrfachauswahl von Fahrzeugen oder Bauteilen werden mehrere Lieferwege mit farblich unterschiedlichen Luftlinien angezeigt. Beim Drüberfahren eines Lieferweges mit dem Mauszeiger wird der Lieferweg farblich hervorgehoben und die Fahrzeug-ID angezeigt.

Die Lieferkettendarstellung wird erst bei Auswahl mindestens eines Bauteils generiert.

Auswahl eines Bauteils und die generierte Lieferkette

Auswahl eines Bauteils und die generierte Lieferkette

Generierte Lieferketten aus mehrfacher Bauteilauswahl

Generierte Lieferketten aus mehrfacher Bauteilauswahl

Popup-Informationen zum Komponentenhersteller

Popup-Informationen zum Komponentenhersteller

Popup-Informationen zum Einzelteilhersteller

Popup-Informationen zum Einzelteilhersteller

Popup-Informationen zum Lieferziel: Fahrzeughalter

Popup-Informationen zum Lieferziel: Fahrzeughalter

Vollständiger Datensatz mit Filterfunktion

Zur Überprüfung der Balkendiagramm- und Kartendarstellung wird der vollständige Datensatz mit allen relevanten Attributen in einer separaten Datentabelle abgebildet.

Zur Nutzerfreundlichkeit generiert diese Datentabelle ebenfalls die Lieferwege in der Kartendarstellung. Die Suche und Auswahl der Daten erfolgt analog zu den o.g. Bauteilsuche.

Vollständiger Datensatz Standardansicht

Vollständiger Datensatz Standardansicht

Auswahl eines Fahrzeugs aus vollständigem Datensatz und Lieferweg

Auswahl eines Fahrzeugs aus vollständigem Datensatz und Lieferweg

Reset-Buttons

Über den “Filter zurücksetzen” Button werden die Filter für die Zulassungsdaten sowie den DataTables für die Gemeinden- und Bauteilauswahl und den vollständigen Datensatz auf ihren Anfangswert zurückgesetzt.

Der Button “Position zurücksetzen” setzt die Ausrichtung und den Zoom der Kartendarstellung auf den Startwert zurück.

Server-Funktionen

Für die übersichtliche und interaktive Darstellung und Auswertung wird Shiny verwendet. Shiny ist ein Paket für R, dass mit R vorbereitete Daten leicht präsentierbar macht. Mit einer Schnittstelle zu HTML über Bootstraps Fluid-System bietet es eine gute Möglichkeit schnell eine responsive Website zu generieren. Diese lässt sich, mit Zuhilfename eines geeigneten Webservers nutzen der Zielgruppe die Daten ansprechend und einfach zu präsentieren.

Gerade bei Datenauswertungen, wie der hier vorliegenden, die zur Aufklärung eines Problems verwendet werden sollen, spielt die Interaktivtät, Daten direkt in der Visuellen Ansicht filtern und vergleichen zu können eine große Rolle. Shiny bietet sogar seinen eigenen kleinen Webserver, falls kein anderer zur verfügung steht.

Generell wird in Shiny die gesamte APP und in zwei Komponenten unterteilt:

# Nicht vollständig oder ausführbar

ui <- fluidPage(
  dataTableOutput("tabelle_1")
)

server <- function(input, output, session) {
  output$tabelle_1 <- renderDataTable(mtcars)
  gefilterte_daten <- mtcars[input$tabelle_1_rows_selected]
}

shinyApp(ui, server)

Die UI wird als geschachtelte Kombination verschiedener bootstrap Elemente kombiniert. Interaktive Inhalte werden über die zwischen Server und Ui geteilten Listen output und input ausgetauscht. So lassen sich UI Elemente über output füllen und durch den User am Interface vorgenommene Auswahlen über input auslesen.

Diese Funktionalität wird in der vorliegenden App für die Auswahl von Daten über die Tabellen und die Datumsauswahlt verwendet.

Der in der Server Funktion platzierte Code wird im Falle von nicht lokal gehosteten webservern auf dem Server statt bei dem Klienten im Browser ausgeführt. Daher bietet es sich beispielsweise auch an Datenintensive Aufgaben wie das durchsuchen des gesamten Datensatzes auf dem Server auszuführen, statt erst die gesamten Daten an den Klienten zu übertragen. Bei unserer App wurde darauf Wert gelegt, möglichst alle Kalkulationen auf Serverseite durchzuführen.

Reaktive Funktionen

Ein weiterer wichtiger Punkt für Interaktivität sind reaktive Funktionen. Diese reagieren auf vom User ausgelöste Events. Der untenstehende Codeblock zeigt unseren Code zum Zurücksetzen des Datumsfilters über einen Button mit der ID “reset_filters”.

# Nicht vollständig oder ausführbar

  observeEvent(
    input$reset_filters,
    updateSliderInput(session, 'slider_zulassungsperiode',
                      value = c(
                        min(all_vehicles$Zulassungsdatum),
                        max(all_vehicles$Zulassungsdatum)
                      ))
  )

Visualisierung

Die Visualisierung der Daten wird über ggplot2 und leaftlet realisiert.

Leaflet

Die Kartendarstellung wurde in drei Kartenebenen unterteilt: Heatmap, Cluster Marker und Lieferwege. Die Ebenen können in der rechten oberen Ecke der Karte an- und abgewählt werden.

Die Heatmap verwendet untere (mindestens 10 fehlerhafte Fahrzeuge) und obere Grenzen (max. 500 fehlerhafte Fahrzeuge) für die Darstellung des Farbbereichs. Dies dient der besseren Visualisierung und Lesbarkeit, da sonst die Karte zu stark rot eingefärbt wäre und fast keine Bereiche ohne Einfärbung dargestellt würden. Die Heatmap kann mit dem Schieberegler auf einen Zeitbereich gefiltert werden.

Die Cluster-Markierungen geben Auskunft über die Ballungsräume mit häufigen Schadensfällen. Durch Zoomen oder Klicken auf die Cluster-Elemente können Fahrzeugdetails über eine Popup-Darstellung aufgerufen werden.

Die Anzeige der Lieferwege ist auf 1000 Verbindungen begrenzt, um einen Absturz der Anwendung zu verhindern. Die Lieferwege lassen sich mithilfe der Tabelle ‘betroffene Bauteile’ oder der Tabelle ‘Datenbank’ an- und abwählen.

Es wird pro ausgewähltem Einzelteil genau ein Lieferweg angezeigt. Dieser besteht aus drei Markern für die beiden Werke (Symbol einer Fabrik) und für das betroffenes Fahrzeug (Marker in rot) und einer Verbindungslinie jenen Markern. Für jede Lieferroute wird eine Verbindungslinie zwischen einem der Einzelteilwerke, dem Komponenten-Werk und dem Standort des Fahrzeugs (bzw. der Koordinaten der jeweiligen Gemeinde) gezogen. Durch Anklicken der Marker können Werks- und Fahrzeugsdetails abgerufen werden.

Aufgetretene Schwierigkeiten

Aufgrund der Größe des Datensatzes entstanden Skalierungsprobleme mit der Verwendung selectizeInput(multiple = TRUE) als Datenfilter. Ab über 50.000 Dateneinträgen traten massive Performance-Probleme, die sich auch durch ein Serverside-Rendering nicht beheben lassen konnten, auf. Bei einem Wechsel zu dataTableInput() konnten wesentliche Funktionen beibehalten werden, wobei sich die App mit dem vollständigen Datensatz problemlos öffnen ließ. Aus diesem Grund wurden bis auf den sliderInput() für die Zulassungsdaten sämtliche Filterfunktionen mittels dataTableInput() umgesetzt.

Ergebnis

Die Zulassungshistorie zeigt, dass die Zahl der betroffenen Fahrzeuge zunimmt, insbesondere ab dem Zeitpunkt, an dem ein Wechsel der Produktionsstandorte erfolgte.

Die Fehleranzahl beim Hersteller 2 der Werke OEM 21 und 22 sind relativ konstant. Ab Mai 2012 hat das Komponentenwerk auch an den Hersteller 1 mit den Werken OEM 11 und 12 defekte Sitze geliefert. Es entstanden ab diesem Zeitpunkt mehr als drei mal so viele Fehler als zu Zeiten der Zusammenarbeit mit OEM 2. Außerdem kam es in manchen Monaten zu extremen Spitzen von fehlerhaften Fahrzeugen.

In der Heatmap-Darstellung ist sehr deutlich zu erkennen, dass es vor allem drei große Ballungsräume mit Schadensschwerpunkten gibt: das Ruhrgebiet, der Großraum München und das Land Sachsen.

Die Darstellung der Lieferwege zeigt, dass es nur ein Komponentenwerk gibt, das relativ in der Mitte Deutschlands liegt. Dieses Werk bezieht Einzelteile aus 11 Werken für die Montage der Ledersitze, welche quer über Deutschland verteilt sind.

Um die Logistik besser abzuwickeln, helfen die Lieferrouten, geeignete Transportwege zu identifizieren und Mehrteillieferungen und ggf. Lagerbestände zu organisieren.

Durch das Altern der Ledersitze ist in den kommenden Jahren mit einem Sprung der Reklamationen zu rechnen. Darauf gilt es sich im Vorhinhein bestmöglich vorzubereiten um die Servicequalität und Liefertreue zu gewährleisten.

Anhang

Zur Ausführung der Shiny-Applikation werden folgende Dateien benötigt:

Komplette Shiny-App

# set cran mirror for package installs
local({r <- getOption("repos")
  r["CRAN"] <- "http://cran.r-project.org" 
  options(repos=r)
})

if (!require(shiny)) {
  install.packages("shiny")
}
library(shiny)

if (!require(ggplot2)) {
  install.packages("ggplot2")
}
library(ggplot2)

if (!require(stringr)) {
  install.packages("stringr")
}
library(stringr)

if (!require(DT)) {
  install.packages("DT")
}
library(DT)

if (!require(scales)) {
  install.packages("scales")
}
library(scales)

if (!require(lubridate)) {
  install.packages("lubridate")
}
library(lubridate)

if (!require(leaflet)) {
  install.packages("leaflet")
}
library(leaflet)

if (!require(leaflet.extras)) {
  install.packages("leaflet.extras")
}
library(leaflet.extras)

if (!require(leafpop)) {
  install.packages("leafpop")
}
library(leafpop)

if (!require(dplyr)) {
  install.packages("dplyr")
}
library(dplyr)

if (!require(glue)) {
  install.packages("glue")
}
library(glue)

# Load manufacturing info with geo data
load("Final_Data_Group_26.RData")

# Subset the data for debugging: reducing the amount of data to be loaded

# final_joined <- final_joined[c(sample(nrow(final_joined), 1001)), ] # shouldn't not be lower than filter limit for markers

# Filter rows to display only distinct ID_Fahrzeug values: fahrzeuge (used in both UI and Server)
all_vehicles <- final_joined[!duplicated(final_joined$ID_Fahrzeug),]

ui <- fluidPage(mainPanel(
  width = "100%",
  
  tags$head(tags$style(
    HTML(
      "
      @import url('//fonts.googleapis.com/css?family=Ubuntu|Cabin:400,700');

      body {
        font-family: 'Ubuntu';
      }

      h2 {
        font-family: 'Ubuntu';
        font-weight: 500;
        line-height: 1.1;
        color: #428BCA;
      }

      #DataTables_Table_1_paginate {
        margin-top: 25px
      }

    "
    )
  )),
  
  # Header panel
  wellPanel(titlePanel("Case_Study_App_26"),
            fluidRow(
              column(
                11,
                "Schadensschwerpunkte und Lieferwege von betroffenen Bauteilen",
                style = 'font-size: 36px; color: #c50e1f;'
              ),
              column(1,
                     imageOutput("Logo", height = 1))
            )),
  
  # Seperate user interface into two tabs for different user groups: owner and manufacturer
  tabsetPanel(
    type = "tabs",
    # Tab with ui for owners
    tabPanel(
      "Für Fahrzeughalter",
      wellPanel(
        titlePanel("Ist mein Fahrzeug betroffen?"),
        fluidRow(
          column(
            4,
            textInput(
              'vehicle_id_input',
              "Fahrzeug ID eingeben um Details zu sehen",
              value = "",
              width = "100%",
              placeholder = 'Fahrzeug ID'
            )
          ),
          column(8,
                 actionButton('vehicle_filter_submit', 'Suchen'),
                 style = "margin-top: 25px"),
        ),
        fluidRow(
          column(
            12,
            verbatimTextOutput("result_text"),
            verbatimTextOutput("vehicle_info_text"),
            tableOutput('components_list'),
            tableOutput('parts_list')
          )
        )
      )
    ),
    # Tab with ui for manufacturer
    tabPanel(
      "Für Fahrzeughersteller",
      
      # Bar plot for Zulassungsverlauf
      wellPanel(
        titlePanel(
          "Zeitlicher Zulassungsverlauf der betroffenen Fahrzeuge aufgeteilt nach OEM-Werken"
        ),
        plotOutput("plot_zulassungsverlauf"),
      ),
      
      # Filter section for bar plot and heat map
      wellPanel(
        titlePanel("Suchfilter zum Anpassen des Balkendiagramms und der Karte"),
        # Reset all filters
        fluidRow(
          column(
            12,
            offset = 0,
            align = 'right',
            #style = 'border: 1px solid lightgray; border-radius: 3px',
            "Zum Filtern der Ergebnisse Bautteile und/oder Gemeinden auswählen",
            actionButton("reset_filters", "Alle Filter zurücksetzen"),
          )
        ),
        
        # Sliderinput filtering the time period for bar plot
        sliderInput(
          "slider_zulassungsperiode",
          "Wählen Sie den Zeitraum der Zulassungen aus",
          min(all_vehicles$Zulassungsdatum),
          max(all_vehicles$Zulassungsdatum),
          value = c(
            min(all_vehicles$Zulassungsdatum),
            max(all_vehicles$Zulassungsdatum)
          )
        ),
        
        # Fluidrow for gemeinden und bauteil datatables incl. search boxes
        fluidRow(column(
          4,
          (h4("Betroffene Gemeinden")),
          
          # Display Betroffene Gemeinden as data table
          dataTableOutput('datatable_gemeinden')
        ),
        column(
          8,
          (h4("Betroffene Bauteile")),
          
          # Display ID-search by ID_einzelteile & ID_Komponente
          dataTableOutput('datatable_bauteile')
        ))
      ),
      
      # heat map with check boxes and cluster markers and Lieferwege on map
      wellPanel(
        titlePanel(
          "Interaktive Karte mit Schadensschwerpunkten, betroffenen Fahrzeugen und Lieferwegen für Ersatzteile"
        ),
        
        fluidRow(
          style = "margin-bottom: 10px;",
          # Reset map position
          column(
            12,
            offset = 0,
            align = 'right',
            "Für mehr Informationen hineinzoomen und/oder auf die Markierungen klicken",
            actionButton(inputId = "reset", "Position zurücksetzen")
          )
        ),
        
        # Display the heatmap with car markers
        leafletOutput(
          outputId = "map",
          width = '100%',
          height = 600
        ),
        #"Bottombox: Zum Anzeigen von Fahrzeuginformationen hineinzoomen und/oder auf die Markierungen klicken"
      ),
      
      # full dataset in a datatable
      wellPanel(titlePanel("Datenbank"),
                fluidRow(
                  column(12,
                         dataTableOutput('datatable_final_joined'),
                         style = 'white-space: nowrap;' # CSS no linebrake in data table column)
                  ))
      )
    )
  )
))
  
# Shiny Server
server <- function(input, output, session) {
  # Render QW_logo: Send a pre-rendered image, and don't delete the image after sending it
  output$Logo <- renderImage({
    # Return a list containining the filename
    list(src = './Zusaetzliche_Dateien/QW_logo.png',
         width = 76,
         height = 56)
  }, deleteFile = FALSE)
  
  
  
  # save start and date of all zulassungen in a vector
  start_end_dates <-
    c(min(all_vehicles$Zulassungsdatum) - 28,
      max(all_vehicles$Zulassungsdatum) + 28)
  
  # Filter parts with the three datatables
  filtered_parts <- reactive({
    # the selections of the tables add to the filtered dataset
    gemeinden_filtered <-
      filter(final_joined, (PLZ %in% gemeinden[input$datatable_gemeinden_rows_selected, ]$PLZ))
    bigtable_filtered <-
      final_joined[input$datatable_final_joined_rows_selected, ]
    bauteiltable_filtered <-
      final_joined[input$datatable_bauteile_rows_selected, ]
    
    final_filtered <-
      bind_rows(gemeinden_filtered,
                bigtable_filtered,
                bauteiltable_filtered)
    
    if (nrow(final_filtered) == 0) {
      final_filtered <- final_joined
    } else {
      # finally the filtered dataset is reduced by the time slider
      final_filtered <-
        subset(
          final_filtered,
          Zulassungsdatum >= input$slider_zulassungsperiode[1] &
            Zulassungsdatum <= input$slider_zulassungsperiode[2]
        )
    }
    
    final_filtered
  })
  
  # Only draw the polylines and overlays for the first n parts
  filtered_parts_limited <- reactive({
    if (nrow(filtered_parts()) <= 1000) {
      out <- filtered_parts()
    } else {
      out <- NULL
    }
    out
  })
  
  # Calculate the vehicles from the filteres parts
  filtered_vehicles <- reactive({
    filtered_parts()[!duplicated(filtered_parts()$ID_Fahrzeug),]
  })
  
  # Filter the Zulassungen so only the ones corresponding to selected Gemeinden in the Gemeinden Datatable are displayed
  zulassungen <- reactive({
    # create subset based on slider input for zulassungen period
    zulassungen_out <-
      subset(
        all_vehicles,
        Zulassungsdatum >= input$slider_zulassungsperiode[1] &
          Zulassungsdatum <= input$slider_zulassungsperiode[2]
      )
    
    # first check wether any rows in the table are selected right now.
    # Selected rows can be checked by appending __rows__selected to the name of a data table and using that as an input
    # This returns the indices of the selected rows in the table, which then need to be mapped to the actual data used in the table
    if (length(input$datatable_gemeinden_rows_selected)) {
      zulassungen_out <-
        filter(all_vehicles, Gemeinde %in% gemeinden[input$datatable_gemeinden_rows_selected, ]$Gemeinde)
    } else {
      # If no rows are selected we use all data
      zulassungen_out <- all_vehicles
    }
    
    # before returning the filtered Zulassungen we are preparing them for use in the bar plot, by bundeling the data by Month, by setting all
    # dates to the first of their month and then grouping them by month, Gemeinde and Fahrzeug ID
    zulassungen_out <- zulassungen_out %>%
      mutate(
        Monat = as.Date(
          format.Date(zulassungen_out$Zulassungsdatum, "%Y-%m-1"),
          "%Y-%m-%d"
        ),
        defekt = (Fehlerhaft_Komponente > 0 |
                    Fehlerhaft_Einzelteil > 0)
      ) %>%
      group_by(Monat, Gemeinde, Werksnummer_Fahrzeug) %>%
      summarise(Anzahl = n()) %>%
      ungroup()
    
    # returning the filtered data
    zulassungen_out
  })
  
  # Render Plot for zeitlichen
  output$plot_zulassungsverlauf <- renderPlot({
    # reactive function to get smaller breaks on x-axis when date range on sliderInput gets smaller
    responsive_break_x <- reactive({
      # difference between sliderInputs is date range in number of days
      if (input$slider_zulassungsperiode[2] - input$slider_zulassungsperiode[1] < 700) {
        responsive_break_x <- breaks_width("1 month")
      } else {
        responsive_break_x <- breaks_width("3 month")
      }
      responsive_break_x
    })
    
    ggplot(zulassungen(), aes(
      x = Monat,
      y = Anzahl,
      fill = factor(Werksnummer_Fahrzeug)
    )) +
      geom_bar(stat = "identity", width = 20) +
      # add fixed colours to fills for each OEM
      scale_fill_manual(values = c(
        "11" = "#c50e1f",
        "12" = "#7CAE00",
        "21" = "#00BFC4",
        "22" = "#C77CFF"
      )) +
      guides(fill = guide_legend(title = "Werksnummer der OEM")) +
      scale_x_date(
        breaks = responsive_break_x(),
        labels = date_format(format = "%Y-%b", tz = "ECT"),
        limits = c(
          input$slider_zulassungsperiode[1] - 40,
          input$slider_zulassungsperiode[2] + 40
        )
      ) +
      # inline function to force breaks to integer values (https://stackoverflow.com/questions/44182709/r-shiny-dashboardsidebar-breaks-graphs-and-tables-width-when-toggling)
      scale_y_continuous(
        breaks = function(x)
          unique(floor(pretty(x)))
      ) +
      theme(
        axis.text.x = element_text(
          angle = 45,
          hjust = 1,
          size = 10
        ),
        axis.text.y = element_text(size = 10),
        axis.title = element_text(size = 14),
        legend.position = "bottom"
      )
  })
  
  # Render data tables: gemeinden / bauteile
  
  gemeinden <- all_vehicles %>%
    select(Gemeinde, PLZ, Werksnummer_Komponente) %>%
    group_by(Gemeinde, PLZ) %>%
    summarise(Zulassungen = length(PLZ)) %>%
    arrange(Gemeinde) %>%
    ungroup()
  
  
  # Render data table: gemeinden
  output$datatable_gemeinden <- renderDataTable({
    input$reset_filters
    datatable(
      gemeinden,
      options = list(
        lengthMenu = list(
          c(3, 6, 10, 20, 100, 1000),
          c('3', '6', '10', '20', '100', '1000')
        ),
        # layout breaks with three digit numbers in the list
        pageLength = 3,
        
        # Search wit regex Ja/Nein
        search = list(
          regex = FALSE,
          caseInsensitive = TRUE,
          search = "Koeln"
        ),
        # ä=ae, ö=oe, ü=ue
        
        # Define German translaton of data table UI
        language = list(
          info = 'Zeige  _START_ bis _END_ von insgesamt _TOTAL_ Ergebnissen',
          paginate = list(
            first = 'Erste',
            last = 'Letzte',
            previous = 'Zurück',
            `next` = 'Vor'
          ),
          infoEmpty = 'Keine Daten vorhanden',
          zeroRecords = 'Keine Ergebnisse gefunden',
          loadingRecords = 'Lädt...',
          processing = 'Ergebnisse werden geladen...',
          lengthMenu = 'Zeige _MENU_ Ergebnisse',
          infoFiltered =  '| Gefiltert von _MAX_ Einträgen',
          search = 'Suche:'
        )
      ),
      rownames = FALSE
    )
  })
  
  
  # show defective parts bools as factors: Ja / Nein
  final_joined$Fehlerhaft_Einzelteil = factor(final_joined$Fehlerhaft_Einzelteil, c(0, 1), c('Nein', 'Ja'))
  final_joined$Fehlerhaft_Komponente = factor(final_joined$Fehlerhaft_Komponente, c(0, 1), c('Nein', 'Ja'))
  
  # Render data table: bauteile
  output$datatable_bauteile <- renderDataTable({
    input$reset_filters
    datatable(
      final_joined[, c(
        'ID_Einzelteil',
        'Werksnummer_Einzelteil',
        'Fehlerhaft_Einzelteil',
        'ID_Komponente',
        'Fehlerhaft_Komponente',
        'ID_Fahrzeug'
      )],
      
      options = list(
        pageLength = 3,
        lengthMenu = list(
          c(3, 6, 10, 20, 100, 1000),
          c('3', '6', '10', '20', '100', '1000')
        ),
        
        # Search options
        search = list(
          regex = FALSE,
          caseInsensitive = TRUE,
          search = ""
        ),
        # ä=ae, ö=oe, ü=ue
        
        # Define German translaton of data table UI
        language = list(
          info = 'Zeige  _START_ bis _END_ von insgesamt _TOTAL_ Ergebnissen',
          paginate = list(
            first = 'Erste',
            last = 'Letzte',
            previous = 'Zurück',
            `next` = 'Vor'
          ),
          infoEmpty = 'Keine Daten vorhanden',
          zeroRecords = 'Keine Ergebnisse gefunden',
          loadingRecords = 'Lädt...',
          processing = 'Ergebnisse werden geladen...',
          lengthMenu = 'Zeige _MENU_ Ergebnisse',
          infoFiltered =  '| Gefiltert von _MAX_ Einträgen',
          search = 'Suche:'
        )
      ),
      
      colnames = c(
        'ID_Werk' = 'Werksnummer_Einzelteil',
        'Fehlerhaft' = 'Fehlerhaft_Einzelteil',
        'Fehlerhaft' = 'Fehlerhaft_Komponente'
      ),
      
      rownames = FALSE
    ) %>%
      formatStyle(c('ID_Komponente', 'ID_Fahrzeug'), `border-left` = 'solid 1px')
  })
  
  #### Data Preparation for the rendering of (1) heatmap with (2) markers and (3) supply routes
  
  # 1. Create datapoints for the heatmap

  threshold_fehleranzahl <- 10 # recommended values: 1, 10, 20, 40, ...
  datapoints_heat <- reactive({
    subset(
      final_joined,
      Zulassungsdatum >= input$slider_zulassungsperiode[1] &
        Zulassungsdatum <= input$slider_zulassungsperiode[2]
    ) %>%
      group_by(Längengrad, Breitengrad) %>%
      summarise(fehleranzahl = n())  %>%
      ungroup()  %>%
      #select(-Gemeinde)  %>%
      filter(fehleranzahl > threshold_fehleranzahl)
  })
  
  # 2.
  # NFilter supply_routes data linked to table selections
  data_dots <- reactive({
    df = data.frame()
    
    if (!is.null(filtered_parts_limited())) {
      supply_routes <- filtered_parts_limited()
      
      df = data.frame(
        id = 1:nrow(supply_routes),
        lat_begin = supply_routes$Breitengrad_Einzelteil,
        lat_via = supply_routes$Breitengrad_Komponente,
        lat_end = supply_routes$Breitengrad,
        lng_begin = supply_routes$Längengrad_Einzelteil,
        lng_via = supply_routes$Längengrad_Komponente,
        lng_end = supply_routes$Längengrad,
        ID_Fahrzeug = supply_routes$ID_Fahrzeug
      )
      #df
    }
  })
  
  # Statistics for tier1 facility
  tier1_werke <- reactive({
    filtered_parts_limited() %>%
      select(
        ID_Fahrzeug,
        ID_Einzelteil,
        Fehlerhaft_Einzelteil,
        Werksnummer_Einzelteil,
        Breitengrad_Einzelteil,
        Längengrad_Einzelteil
      ) %>%
      group_by(Werksnummer_Einzelteil,
               Breitengrad_Einzelteil,
               Längengrad_Einzelteil) %>%
      summarise(
        'Einzelteile geliefert' = n(),
        
        'fehlerhaft laut Einzelteil-Werk' = length(Fehlerhaft_Einzelteil[Fehlerhaft_Einzelteil == 'Ja']),
        Einzelteile = substring(str_c(
          glue('<br>{ID_Einzelteil}'), collapse = ""
        ), 5),
        Fehlerhaft = toString(Fehlerhaft_Einzelteil) # if not converted from boolean then: toString(ifelse(Fehlerhaft_Einzelteil == 1, 'Ja', 'Nein'))
      ) %>%
      ungroup() %>%
      arrange(Fehlerhaft)
  })
  
  # Statistics for tier2 facility
  tier2_werke <- reactive({
    filtered_parts_limited() %>%
      select(
        ID_Fahrzeug,
        ID_Fahrzeug,
        ID_Komponente,
        Fehlerhaft_Einzelteil,
        Fehlerhaft_Komponente,
        Werksnummer_Komponente,
        Breitengrad_Komponente,
        Längengrad_Komponente
      ) %>%
      group_by(Werksnummer_Komponente,
               Breitengrad_Komponente,
               Längengrad_Komponente) %>%
      summarise(
        'Einzelteile erhalten' = n(),
        'fehlerhaft laut Einzelteil-Werk' = length(Fehlerhaft_Einzelteil[Fehlerhaft_Einzelteil == 'Ja']),
        'Defekte Sitze hergestellt' = sum(!duplicated(ID_Fahrzeug)),
        'fehlerhaft laut Komponenten-Werk' = length(Fehlerhaft_Komponente[Fehlerhaft_Komponente == 'Ja']),
        Komponenten = substring(str_c(
          glue('<br>{ID_Komponente}'), collapse = ""
        ), 5),
        Fehlerhaft = toString(Fehlerhaft_Komponente) # if not converted from boolean then: toString(ifelse(Fehlerhaft_Komponente == 1, 'Ja', 'Nein'))
      ) %>%
      ungroup()
  })
  
  # Colors for the supply routes (n = 10)
  colors_polyline <-
    c(
      "#c50e1f",
      "#7CAE00",
      "#00BFC4",
      "#C77CFF",
      "#D5BF86",
      "#541C52",
      "#CA6000",
      "#CAAC00",
      "#000000",
      "#FFFFFF"
    )
  
  # Render map
  output$map <- renderLeaflet({
    filtered_data_dots <- data_dots()
    leaflet_map <- leaflet(final_joined) %>%
      setView(lng = 10.46,
              lat = 51.15,
              zoom = 6.25) %>% # centered to Germany map
      # do not use:
      #fitBounds(min(final_joined$Längengrad, na.rm = TRUE),min(final_joined$Breitengrad, na.rm = TRUE),max(final_joined$Längengrad, na.rm = TRUE),max(final_joined$Breitengrad, na.rm = TRUE)) %>% # buggy after scaling
      addTiles() %>%
      
      # Layer 1: Heatmap
      addHeatmap(
        data = datapoints_heat(),
        lng = ~ Längengrad,
        lat = ~ Breitengrad,
        intensity = ~ fehleranzahl,
        blur = 12,
        max = 500,
        radius = 16,
        group = "Heatmap"
      ) %>% # intensity = ~fehleranzahl, blur = 14, max = 60, radius = 12) %>%
      
      
      # Layer 2: fehlerhafte Fahrzeuge
      addMarkers(
        data = filtered_vehicles(),
        ~ Längengrad,
        ~ Breitengrad,
        group = "Cluster Marker",
        #display large amounts of markers as clusters
        clusterOptions = markerClusterOptions(),
        popup = ~ paste(
          "<center><h5>Betroffenes Fahrzeug</h5></center>",
          "ID_Fahrzeug: ",
          ID_Fahrzeug,
          "<br/>",
          "ID_Sitz: ",
          ID_Komponente,
          "<br/>",
          "Baujahr: ",
          format(as.Date(Produktionsdatum_Fahrzeug), "%Y"),
          "<br/>",
          "Zulassung am: ",
          format(as.Date(Zulassungsdatum), "%d.%m.%Y"),
          "<br/>",
          "Zugelassen in: ",
          PLZ,
          " ",
          Gemeinde
        )
      )
    
    # Layer 3: Lieferwege
    # Render the polyroutes supply route
    if (!is.null(filtered_data_dots)) {
      for (i in 1:nrow(filtered_data_dots)) {
        leaflet_map <-
          addPolylines(
            leaflet_map,
            data = filtered_data_dots[i, ],
            group = "Lieferwege",
            lng = ~ c(lng_begin, lng_via, lng_end),
            lat = ~ c(lat_begin, lat_via, lat_end),
            color = colors_polyline[i %% 10],
            # iterates the 10 colors over and over again
            weight = 4,
            opacity = 0.5,
            fillColor = "#c50e1",
            fillOpacity = 0.5,
            smoothFactor = 1,
            popup = ~ ID_Fahrzeug,
            label = ~ ID_Fahrzeug,
            #labelOptions = NULL, options = pathOptions(),
            highlightOptions = highlightOptions(
              color = "white",
              weight = 2,
              bringToFront = TRUE
            )
          )
      }
      
      
      # Layer 3: Lieferwege
      
      # Add circles of facility
      facitily_group_name = "Lieferwege"
      # Einzelteil-Werk: Number of production errors Einzelteile hergestellt (schwarz)
      leaflet_map <- leaflet_map %>%
        
        #Display tier1 facilities with custom icon
        addMarkers(
          data = tier1_werke(),
          ~ Längengrad_Einzelteil,
          ~ Breitengrad_Einzelteil,
          group = facitily_group_name,
          icon = ~ icons(
            iconUrl = './Zusaetzliche_Dateien/facility_icon.png',
            iconWidth = 40,
            iconHeight = 25,
          ),
          #display large amounts of markers as clusters
          #clusterOptions = markerClusterOptions(freezeAtZoom = 7),
          popup = ~ paste(
            "<center><h5>Einzelteil-Werk</h5></center>",
            popupTable(
              tier1_werke(),
              feature.id = FALSE,
              row.numbers = FALSE,
              zcol = c(
                'Werksnummer_Einzelteil',
                'Einzelteile geliefert',
                'fehlerhaft laut Einzelteil-Werk',
                "Einzelteile",
                "Fehlerhaft"
              )
            )
          ),
          popupOptions = popupOptions(minWidth = 320, maxHeight = 500)
          
        )  %>%
        
        # Display tier2 facilities with custom icon
        addMarkers(
          data = tier2_werke(),
          ~ Längengrad_Komponente,
          ~ Breitengrad_Komponente,
          group = facitily_group_name,
          icon = ~ icons(
            iconUrl = './Zusaetzliche_Dateien/facility_icon.png',
            iconWidth = 40,
            iconHeight = 25,
          ),
          popup = ~ paste(
            "<center><h5>Komponenten-Werk</h5></center>",
            popupTable(
              tier2_werke(),
              feature.id = FALSE,
              row.numbers = FALSE,
              zcol = c(
                'Werksnummer_Komponente',
                'Einzelteile erhalten',
                'fehlerhaft laut Einzelteil-Werk',
                'Defekte Sitze hergestellt',
                'fehlerhaft laut Komponenten-Werk',
                'Komponenten',
                'Fehlerhaft'
              )
            )
          ),
          popupOptions = popupOptions(minWidth = 360, maxHeight = 500)
        )
      
      # Add marker for car location
      filtered_vehicles_tmp <- filtered_parts_limited()
      
      if (!is.null(filtered_parts_limited)) {
        for (i in 1:nrow(filtered_vehicles_tmp)) {
          leaflet_map <-
            addMarkers(
              leaflet_map,
              data = filtered_vehicles_tmp[i,],
              ~ Längengrad,
              ~ Breitengrad,
              group = "Lieferwege",
              icon = ~ icons(
                iconUrl = './Zusaetzliche_Dateien/marker_icon.png',
                iconWidth = 35,
                iconHeight = 40,
                iconAnchorX = 17,
                iconAnchorY = 40
              ),
              #display large amounts of markers as clusters
              clusterOptions = markerClusterOptions(),
              popup = ~ paste(
                "<center><h5>Betroffenes Fahrzeug</h5></center>",
                "ID_Fahrzeug: ",
                ID_Fahrzeug,
                "<br/>",
                "ID_Sitz: ",
                ID_Komponente,
                "<br/>",
                "Baujahr: ",
                format(as.Date(Produktionsdatum_Fahrzeug), "%Y"),
                "<br/>",
                "Zulassung am: ",
                format(as.Date(Zulassungsdatum), "%d.%m.%Y"),
                "<br/>",
                "Zugelassen in: ",
                PLZ,
                " ",
                Gemeinde
              )
            )
        }
      }
    }
    
    # Layer control
    leaflet_map <- leaflet_map %>%
      addLayersControl(
        overlayGroups = c("Heatmap", "Cluster Marker", "Lieferwege"),
        options = layersControlOptions(collapsed = FALSE)
      ) %>%
      hideGroup("Heatmap")
    
    # Return leaflet_map with all layers to render_leaflet()
    leaflet_map
  })
  
  # Render full database
  output$datatable_final_joined <- renderDataTable({
    input$reset_filters
    datatable(
      final_joined[, c(
        'ID_Einzelteil',
        'Werksnummer_Einzelteil',
        'Fehlerhaft_Einzelteil',
        'ID_Komponente',
        'Werksnummer_Komponente',
        'Fehlerhaft_Komponente',
        'ID_Fahrzeug',
        'Werksnummer_Fahrzeug',
        'Produktionsdatum_Fahrzeug',
        'Zulassungsdatum',
        'Gemeinde',
        'PLZ'
      )],
      
      filter = list(position = 'top', clear = TRUE),
      
      options = list(
        pageLength = 10,
        lengthMenu = list(
          c(3, 10, 20, 100, 1000, 10000),
          c('3', '10', '20', '100', '1000', '10000')
        ),
        
        # Search wit regex Ja/Nein
        search = list(
          regex = FALSE,
          caseInsensitive = TRUE,
          search = ""
        ),
        # ä=ae, ö=oe, ü=ue
        
        # Define German translaton of data table UI
        language = list(
          info = 'Zeige  _START_ bis _END_ von insgesamt _TOTAL_ Ergebnissen',
          paginate = list(
            first = 'Erste',
            last = 'Letzte',
            previous = 'Zurück',
            `next` = 'Vor'
          ),
          infoEmpty = 'Keine Daten vorhanden',
          zeroRecords = 'Keine Ergebnisse gefunden',
          loadingRecords = 'Lädt...',
          processing = 'Ergebnisse werden geladen...',
          lengthMenu = 'Zeige _MENU_ Ergebnisse',
          infoFiltered =  '| Gefiltert von _MAX_ Einträgen',
          search = 'Suche:'
        )
      ),
      
      colnames = c(
        'ID_Werk' = 'Werksnummer_Einzelteil',
        'ID_Werk' = 'Werksnummer_Komponente',
        'ID_Werk' =  'Werksnummer_Fahrzeug',
        'Fehlerhaft' = 'Fehlerhaft_Einzelteil',
        'Fehlerhaft' = 'Fehlerhaft_Komponente'
      ),
      
      rownames = FALSE
    ) %>%
      formatStyle(c('ID_Komponente', 'ID_Fahrzeug'), `border-left` = 'solid 1px',)
  })
  
  # resetting map position
  observe({
    input$reset
    leafletProxy("map") %>%
      setView(lng = 10.46,
              lat = 51.15,
              zoom = 6.25)
  })
  
  
  # reset sliderInput for zulassungen period
  observeEvent(
    input$reset_filters,
    updateSliderInput(session, 'slider_zulassungsperiode',
                      value = c(
                        min(all_vehicles$Zulassungsdatum),
                        max(all_vehicles$Zulassungsdatum)
                      ))
  )
  
  # Overview for car owner with infos about their vehicle and the defective parts
  output$result_text <-
    renderText({
      "Geben Sie ihre Fahrzeug ID in die Suche ein um zu überprüfen ob ihr Fahrzeug betroffen ist."
    })
  
  observeEvent(input$vehicle_filter_submit, {
    vehicle_parts <- filter(final_joined, ID_Fahrzeug == input$vehicle_id_input)
    if (nrow(vehicle_parts) >= 1) {
      output$result_text <- renderText({"Ihr Fahrzeug ist betroffen"})
      #vehicle <- vehicle_parts[!duplicated(vehicle_parts$ID_Fahrzeug)]
      vehicle <- vehicle_parts[1,]
      vehicle_info_string <-
      glue(
        "Ihr Fahrzeug (ID: {vehicle$ID_Fahrzeug}), zugelassen am {format(vehicle$Zulassungsdatum, '%d.%m.%Y')} in {vehicle$PLZ} {vehicle$Gemeinde},
wurde am {format(vehicle$Produktionsdatum_Fahrzeug, '%d.%m.%Y')} im Werk {vehicle$Werksnummer_Fahrzeug} gebaut.
Folgend finden sie die Auflistung zu der verbauten Sitzgruppe, sowie zu den dafür verwendeten Einzelteilen
zusammen mit den Werksnummern bei denen Ihre Servicewerkstatt Ersatzteile anfordern kann."
        )
      output$vehicle_info_text <- renderText(vehicle_info_string)
      
      # components
      output$components_list <-
        renderTable(vehicle_parts[, c("ID_Komponente",
                                      "Fehlerhaft_Komponente",
                                      "Werksnummer_Komponente")], digits = 0)
      # parts
      output$parts_list <-
        renderTable(vehicle_parts[, c("ID_Einzelteil",
                                      "Fehlerhaft_Einzelteil",
                                      "Werksnummer_Einzelteil")], digits = 0)
      
    } else {
      output$result_text <- renderText({
        "ID exisitiert nicht."
      })
      output$vehicle_info_text <- NULL
      output$components_list <- NULL
      output$parts_list <- NULL
    }
  })
}

# Shiny App starten
shinyApp(ui, server)